Oracle Research on paging method

  • 1、Oracle Three paging methods in      
    • 1.1、 By analyzing function paging
    • 1.2、 adopt ROWNUM Pagination
    • 1.3、 adopt ROWID Pagination
  • 2、Oracle Paging solution analysis      
    • 2.1、 Pure back-end code completes paging
    • 2.2、 Paging through stored procedures
    • 2.3、 Two common paging stored procedures
  • 3、 summary

1、Oracle Three paging methods in

I recently summed up Oracle Pagination in , From pure SQL In writing , Paging is nested subquery , It’s just that different paging methods have different levels of nested subqueries .Oracle There are three kinds of pagination in , Namely : Nested analysis function of a layer of sub queries 、 Nested two layers of subqueries ROWNUM Paging and nesting three levels of sub query ROWID Pagination .

1.1、 By analyzing function paging

Sort by employee age , Each page shows 3 Employees , Take the first place 1 Pages of data . Nested only one level of subqueries , The writing is concise , Easy to understand , But generally no one uses this method . The sorting function can be realized by sorting within the analysis function of the subquery .

SELECT t2.staff_name,t2.birthday FROM( SELECT t1.staff_name,t1.birthday,ROW_NUMBER() OVER(ORDER BY t1.birthday) rn FROM demo.t_staff t1
) t2 WHERE t2.rn >= ((1-1)*3+1) AND t2.rn <= (1*3);

1.2、 adopt ROWNUM Pagination

Sort by employee age , Each page shows 3 Employees , Take the first place 1 Pages of data . Nested two levels of subqueries , The writing is flexible , It’s usually this way . You only need to sort inside the subquery to realize the sorting function .

SELECT t3.staff_name,t3.birthday FROM( SELECT t2.*,ROWNUM rn FROM( SELECT t1.staff_name,t1.birthday FROM demo.t_staff t1 ORDER BY t1.birthday
  ) t2 WHERE ROWNUM <= (1*3)
) t3 WHERE t3.rn >= ((1-1)*3+1);

adopt ROWNUM An alternative to pagination ( It’s relatively better to understand ):

SELECT t3.staff_name,t3.birthday FROM( SELECT t2.*,ROWNUM rn FROM( SELECT t1.staff_name,t1.birthday FROM demo.t_staff t1 ORDER BY t1.birthday
  ) t2
) t3 WHERE t3.rn >= ((1-1)*3+1) AND t3.rn <= (1*3);

1.3、 adopt ROWID Pagination

Sort by employee age , Each page shows 3 Employees , Take the first place 1 Pages of data . The writing is complicated , Not very flexible , Not easy to understand , Very few people use this method . You must sort in both the innermost sub query and the outermost query to realize the sorting function .

SELECT t4.staff_name,t4.birthday FROM demo.t_staff t4 WHERE t4.ROWID IN( SELECT t3.rid FROM( SELECT t2.rid,ROWNUM rn FROM( SELECT t1.ROWID rid FROM demo.t_staff t1 ORDER BY t1.birthday
    ) t2 WHERE ROWNUM <= (1*3)
  ) t3 WHERE t3.rn >= ((1-1)*3+1)
) ORDER BY t4.birthday;

2、Oracle Paging solution analysis

Oracle The second is the most widely used of the three paging methods in , That is based on ROWNUM The pagination method of . Because the syntax for pagination is fixed , Therefore, a common paging template method will be provided in general projects , Then other business methods that need paging call this method to complete the paging function .

The implementation process of paging is splicing SQL Statement procedure , But there’s also a choice to complete it in that place . Generally speaking, splicing on the server side is a better choice , The main advantage of this scheme is flexibility 、 Simple 、 Easy maintenance . Another common method is paging through stored procedures , Then call the stored procedure on the server side , In theory, this scheme has high paging efficiency , But the implementation process is relatively complex , It’s not as easy to maintain as pure server-side code .

2.1、 Pure back-end code completes paging

Pure back-end code completes paging in the definition 、 call 、 performance 、 understand 、 There are a lot of small skills in maintenance, etc . A few days ago, I combined my paging experience over the years with a technical expert who has worked in the company for more than ten years to exchange this problem , In the end, we agreed that the best way is to pass the whole inner layer sub query ( It can avoid a lot of small pits ). The splicing format is as follows :

SELECT t3.* FROM(
  SELECT t2.*,ROWNUM rn FROM( :subquery ) t2 WHERE ROWNUM <= (:pageIndex*:pageSize)
) t3 WHERE t3.rn >= ((:pageIndex-1)*:pageSize+1)

We’ve all tried to split subqueries into parts before , And then the way they are delivered separately , But once the project gets deeper, there are more problems than you think . For example, too many parameters make calling more difficult , In order to achieve pagination, we have to split the whole statement into several parts, which is a waste of time , When something goes wrong, the complexity of debugging also increases , Multi table paging is also relatively difficult to handle , Inexperienced programmers often have no patience to understand the existing code, and then fabricate a so-called improved version ( In fact, there are still many such situations )……

But even if the whole subquery comes in , There will still be different ways of dealing with it . For example, the expert I mentioned above said that they had tried to split the incoming subquery into multiple parts and then recombine it , But later found that complex subqueries are extremely difficult to write right , It just increases the frustration of the new people in the team ……

The asterisk in the outer query is the key point , Although we all know that asterisks in queries are often bad , But if you still stick to this point when pagination , It’s bound to lead to complex splicing . Complex stitching is often hard to write , It’s also error prone when calling , From time to time, I have to look back at the internal implementation and deduce how to call it , This process is obviously a waste of time .

2.2、 Paging through stored procedures

Most of the time, I use stored procedures to achieve paging , However, it is difficult for many people to write or even call stored procedures , I think the main reason is that I am not familiar with the relevant knowledge 、 Write less . The following is a list of related reference connections for writing paging stored procedures and calling stored procedures :

  • 《.Net Programmers learn to use Oracle series (7): View 、 function 、 stored procedure 、 package 》: stored procedure
  • 《.Net Programmers learn to use Oracle series (26):PLSQL The types of 、 Variables and structures 》: Variable
  • 《.Net Programmers learn to use Oracle series (27):PLSQL The cursor of 、 Exceptions and transactions 》: The cursor
  • 《.Net Programmers learn to use Oracle series (16): Access database (ODP.NET)》: Oracle provides the drive

Here is a call Oracle Of paged stored procedures C# Method :

/// <summary> ///  Calling stored procedure , Perform paging  /// </summary> /// <param name="tableName"> Table name </param> /// <param name="queryFields"> Inquire about ( Field ) list </param> /// <param name="queryWhere"> Query criteria </param> /// <param name="orderBy"> Sort clause </param> /// <param name="pageIndex"> Page index ( Page number )</param> /// <param name="pageSize"> Page size ( Number of data bars per page )</param> /// <param name="pageCount"> Total number of pages </param> /// <param name="rowCount"> Total number of lines </param> /// <param name="resultSet"> Result set </param> public void ExecutePaging( string tableName, string queryFields, string queryWhere, string orderBy, int pageIndex, int pageSize, ref int pageCount, ref int rowCount, ref DataTable resultSet) {
    OracleParameter[] ps = { new OracleParameter(":tableName", OracleDbType.Varchar2, 1000), new OracleParameter(":queryFields", OracleDbType.Varchar2, 1000), new OracleParameter(":queryWhere", OracleDbType.Varchar2, 2000), new OracleParameter(":orderBy", OracleDbType.Varchar2, 200), new OracleParameter(":pageIndex", OracleDbType.Int32), new OracleParameter(":pageSize", OracleDbType.Int32), new OracleParameter(":pageCount", OracleDbType.Int32), new OracleParameter(":rowCount", OracleDbType.Int32), new OracleParameter(":resultSet", OracleDbType.RefCursor)
    };
    ps[0].Value = tableName;
    ps[1].Value = queryFields;
    ps[2].Value = queryWhere;
    ps[3].Value = orderBy;
    ps[4].Value = pageIndex;
    ps[5].Value = pageSize;
    ps[6].Direction = ParameterDirection.Output;
    ps[7].Direction = ParameterDirection.Output;
    ps[8].Direction = ParameterDirection.Output;
    resultSet = OracleHelper.ProcQuery("sp_dynamic_paging", ps); //  Calling stored procedure  pageCount = Verifier.VerifyInt(ps[6].Value);
    rowCount = Verifier.VerifyInt(ps[7].Value);
}

2.3、 Two common paging stored procedures

The following stored procedure is extracted from a project I was responsible for , It’s also my first attempt to write stored procedure paging ,100% original , There have been several revisions in the middle , For the convenience of reading, the notes have been removed by me , Now in this version i_queryFields Parameters don’t accept asterisks :

CREATE OR REPLACE PROCEDURE sp_paging(
  i_tableName VARCHAR2, --  Table name
  i_queryFields VARCHAR2, --  Inquire about ( Field ) list
  i_queryWhere VARCHAR2, --  Query criteria
  i_orderBy VARCHAR2, --  Sort clause
  i_pageIndex NUMBER, --  Current page index
  i_pageSize NUMBER, --  Page size
  o_rowCount OUT NUMBER, --  Total number of lines
  o_pageCount OUT NUMBER, --  Total number of pages
  o_resultSet OUT SYS_REFCURSOR --  Result set
) IS
  v_count_sql VARCHAR2(2000);
  v_select_sql VARCHAR2(4000); BEGIN --  Splice the statements to query the total number of rows
    v_count_sql := 'SELECT COUNT(1) FROM '||i_tableName;
    --  Splicing query criteria  IF i_queryWhere IS NOT NULL THEN
      v_count_sql := v_count_sql||' WHERE 1=1 '||i_queryWhere; END IF;
    --  Calculate the total number
    EXECUTE IMMEDIATE v_count_sql INTO o_rowCount;
    --DBMS_OUTPUT.PUT_LINE(v_count_sql||';');
    --  Calculate the total number of pages (CEIL  Rounding up )
    o_pageCount := CEIL(o_rowCount / i_pageSize);
    --  If there's a record , And the current page index is legal , Then continue to query  IF o_rowCount >= 1 AND i_pageIndex >= 1 AND i_pageIndex <= o_pageCount THEN
      --  When the total number of records is less than or equal to the page size , Check all records  IF o_rowCount <= i_pageSize THEN
        v_select_sql := 'SELECT '||i_queryFields||' FROM('||i_tableName||')'; IF i_queryWhere IS NOT NULL THEN
          v_select_sql := v_select_sql||' WHERE 1=1 '||i_queryWhere; END IF; IF i_orderBy IS NOT NULL THEN
          v_select_sql := v_select_sql||' order by '||i_orderBy; END IF;
      --  Check the first page
      ELSIF i_pageIndex = 1 THEN
        v_select_sql := 'SELECT '||i_queryFields||' FROM('||i_tableName||')'; IF i_queryWhere IS NOT NULL THEN
          v_select_sql := v_select_sql||' WHERE 1=1 '||i_queryWhere; END IF; IF i_orderBy IS NOT NULL THEN
          v_select_sql := v_select_sql||' order by '||i_orderBy; END IF;
        v_select_sql := 'SELECT '||i_queryFields||' FROM('||v_select_sql||') WHERE ROWNUM<='||i_pageSize;
      --  Query the specified page  ELSE v_select_sql := 'SELECT '||i_queryFields||' FROM('||i_tableName||')'; IF i_queryWhere IS NOT NULL THEN
          v_select_sql := v_select_sql||' WHERE 1=1 '||i_queryWhere; END IF; IF i_orderBy IS NOT NULL THEN
          v_select_sql := v_select_sql||' order by '||i_orderBy; END IF;
        v_select_sql := 'SELECT '||i_queryFields||' FROM(SELECT ROWNUM rn,'||i_queryFields||' FROM('||v_select_sql
          ||')) WHERE rn>'||((i_pageIndex-1)*i_pageSize)||' AND rn<='||(i_pageIndex*i_pageSize); END IF;
      --DBMS_OUTPUT.PUT_LINE(v_select_sql||';');
      OPEN o_resultSet FOR v_select_sql; ELSE OPEN o_resultSet FOR 'SELECT * FROM '||i_tableName||' WHERE 1!=1'; END IF; END;

The following stored procedure is taken from 《 The sword breaks the iceberg ——Oracle Developing art 》 A Book , There is a deletion :

OR REPLACE PROCEDURE sp_dynamic_paging(
  i_tableName VARCHAR2, --  Table name
  i_queryFields VARCHAR2, --  Query list
  i_queryWhere VARCHAR2, --  Query criteria
  i_orderBy VARCHAR2, --  Sort
  i_pageSize NUMBER, --  Page size
  i_pageIndex NUMBER, --  Page index
  o_rowCount OUT NUMBER, --  Return the total number
  o_pageCount OUT NUMBER, --  Returns the total number of pages
  o_resultSet OUT SYS_REFCURSOR --  Returns the paged result set
) IS v_startRows INT; --  Go ahead
  v_endRows INT; --  End line  
  v_pageSize INT;
  v_pageIndex INT;
  v_queryFields VARCHAR2(2000);
  v_queryWhere VARCHAR2(2000);
  v_orderBy VARCHAR2(200);
  v_count_sql VARCHAR2(1000); --  Receiving the number of statistics  SQL  sentence
  v_select_sql VARCHAR2(4000); --  Receive query paging data  SQL  sentence
BEGIN
  --  If there is no table name , The exception message is returned directly
  --  If there are no fields , It means to query all fields  IF i_queryFields IS NOT NULL THEN v_queryFields:=i_queryFields; ELSE v_queryFields:=' * '; END IF;
  --  There can be no query conditions  IF i_queryWhere IS NOT NULL THEN v_queryWhere := ' WHERE 1=1 AND'||i_queryWhere||' '; ELSE v_queryWhere := ' WHERE 1=1 '; END IF;
  --  There can be no sort conditions  IF i_orderBy IS NULL THEN v_orderBy:=' '; ELSE v_orderBy:='ORDER BY '||i_orderBy; END IF;
  --  If no query page is specified , The default is home page  IF i_pageIndex IS NULL OR i_pageIndex<1 THEN v_pageIndex:=1; ELSE v_pageIndex:=i_pageIndex; END IF;
  --  If the number of records per page is not specified , The default is  10  strip  IF i_pageSize IS NULL THEN v_pageSize:=10; ELSE v_pageSize:=i_pageSize; END IF;
  --  Construct the total number of queries
  v_count_sql:='SELECT COUNT(1) FROM '||i_tableName||v_queryWhere; --DBMS_OUTPUT.PUT_LINE(v_count_sql||';'); --  Construct statements to query data
  v_select_sql:='(SELECT '||v_queryFields||' FROM '||i_tableName||v_queryWhere||v_orderBy||') t2'; --  Total number of queries
  EXECUTE IMMEDIATE v_count_sql INTO o_rowCount;
  --  Get the total number of pages  IF MOD(o_rowCount,i_pageSize)=0 THEN o_pageCount:=o_rowCount/i_pageSize; ELSE o_pageCount:=FLOOR(o_rowCount/i_pageSize)+1; END IF;
  --  If the current page is greater than the maximum number of pages , Take the maximum number of pages  IF i_pageIndex>o_pageCount THEN v_pageIndex:=o_pageCount; END IF;
  --  Set the number of records that start and end
  v_startRows:=(v_pageIndex-1)*v_pageSize+1;
  v_endRows:=v_pageIndex*v_pageSize;
  --  The dynamics of completion  SQL  Statement splicing
  v_select_sql:='SELECT t3.* FROM'||'(SELECT t2.*,ROWNUM rn FROM'||v_select_sql ||' WHERE ROWNUM<='||v_endRows||') t3 WHERE t3.rn>='||v_startRows; --DBMS_OUTPUT.PUT_LINE(v_select_sql||';'); OPEN o_resultSet FOR v_select_sql; END;

The following paragraph PL/SQL The code is used to test the above two stored procedures :

DECLARE v_tableName VARCHAR2(1000);
  v_queryFields VARCHAR2(1000);
  v_queryWhere VARCHAR2(1000);
  v_orderBy VARCHAR2(200);
  v_pageSize INT := 3;
  v_pageIndex INT;
  v_rowCount INT := 0;
  v_pageCount INT := 0;
  v_resultSet SYS_REFCURSOR;
BEGIN
  v_tableName:='t_staff';
  v_queryFields:='staff_name,birthday';
  v_orderBy:='birthday';
  v_pageIndex:=1;
  sp_dynamic_paging(
    i_tableName => v_tableName,
    i_queryFields => v_queryFields,
    i_queryWhere => v_queryWhere,
    i_orderBy => v_orderBy,
    i_pageSize => v_pageSize,
    i_pageIndex => v_pageIndex,
    o_rowCount => v_rowCount,
    o_pageCount => v_pageCount,
    o_resultSet => v_resultSet
  );
END;

3、 summary

This article mainly describes Oracle Three paging methods in and two common paging solutions , And gives two general paging stored procedure source code . It’s mainly about what I have personally Oracle This is a comprehensive review of paging methods and techniques .