Summary of oracle Database paging query instances

When I first came into contact with the oracle database, I learned about the oracle paging query function. Next I will share with you some of my found and self-developed replice paging query instances, I hope to help you.

Oracle paging query statements can be applied in the format given in this article.
 

Paging Query format:

The Code is as follows:	Copy code
SELECT * FROM (select a. *, ROWNUM RN FROM (SELECT * FROM TABLE_NAME) a where rownum <= 40) where rn> = 21

 

SELECT * FROM TABLE_NAME indicates the original query statement that does not flip pages. ROWNUM <= 40 and RN> = 21 control the range of each page of the paging query.

 

The paging query statement given above has a high efficiency in most cases. The purpose of paging is to control the size of the output result set and return the result as soon as possible. In the preceding paging query statement, this consideration is mainly reflected in the where rownum <= 40 sentence.

 

There are two methods to select 21st to 40 records. One is that the second layer of the query in the example above uses ROWNUM <= 40 to control the maximum value, the minimum value is controlled at the outermost layer of the query. The other method is to remove the where rownum <= 40 Statement on the second layer of the query, and control the minimum and maximum paging values at the outermost layer of the query. Here is the query statement:

The Code is as follows:	Copy code
SELECT * FROM (select a. *, ROWNUM RN FROM (SELECT * FROM TABLE_NAME) A) where rn between 21 AND 40

 

In most cases, the efficiency of the first query is much higher than that of the second query.

 
This is because in the CBO optimization mode, Oracle can push the outer query conditions to the inner query to improve the execution efficiency of the inner query. For the first query statement, the SQL query condition WHERE ROWNUM <= 40 can be pushed to the inner query by Oracle. In this way, once the query result of Oracle exceeds the ROWNUM limit, the query is terminated and the result is returned.

 

The second query statement, because the query conditions BETWEEN 21 AND 40 exist on the third layer of the query, oracle cannot push the layer-3 query conditions to the innermost layer (even pushing to the innermost layer makes no sense, because the innermost layer query does not know what RN represents ). Therefore, for the second query statement, the oldest layer of Oracle returns all the data that meets the conditions to the middle layer, and the data that the middle layer returns to the outermost layer is all the data. Data filtering is completed at the outermost layer. Obviously, this efficiency is much lower than the first query.
 

The query analyzed above is not only a simple query for a single table, but also effective for complex multi-table joint queries or sorting in the innermost query.

The query that contains sorting is not described here, and the next article will explain in detail through examples. Next we will briefly discuss the situation of Multi-table join. For the most common equi JOIN queries, CBO generally uses two JOIN Methods: nested loop and hash join (merge join is less efficient than hash join, which is not considered by CBO in general ). Because paging is used, the maximum number of records returned is specified. When the number of returned records exceeds the maximum value, the nested loop can immediately stop and return the results to the intermediate layer, however, hash join must process all result sets (so does merge join ). In most cases, it is highly efficient to select nested loop as the query connection method for paging queries (most of the time when querying by page is the data of the first few pages, the lower the page number, the lower the chance of access ).

 
Therefore, if you don't mind using HINT in the system, you can rewrite the paging query statement:
 

The Code is as follows:	Copy code
SELECT/* + FIRST_ROWS */* FROM (select a. *, rownum rn from (SELECT * FROM TABLE_NAME) a where rownum <= 40) where rn> = 21

So I wrote an instance with php.

 

The Code is as follows:

Copy code

<? PHP /*************************************** ****** TOracleViewPage V2.0 Date: 2000-9-23 Categories of Oracle Database records displayed on pages Updated on: 2000-10-19 Added the TopRecord display function, allowing the number of records displayed on the first page to be different from those displayed on other pages. Author: sharetop Email: ycshowtop@21cn.com **************************************** *******/ Class TOracleViewPage { Var $ Table; // Table name Var $ MaxLine; // number of lines per page Var $ LinkId; // database connection number Var $ Id; // sorting Reference Field Var $ Offset; // record Offset Var $ Total; // The Total number of records. Var $ Number; // Number of records read on the current page Var $ TopNumber; // number of records actually retrieved when reading a new record Var $ Result; // read the Result Var $ TopResult; // The result when the new record is read. Var $ TheFirstPage; // specify the link to the first page. Var $ StartRec; // specify the start record number on the second page Var $ TPages; // the total number of pages. Var $ CPages; // current page number Var $ TGroup; Var $ PGroup; // number of page numbers displayed on each page Var $ CGroup; Var $ Condition; // display conditions such as: where id = '$ id' order by id desc Var $ PageQuery; // display the parameters to be passed by PAGE //------------------------------------- // The following constructor, destructor, and initialization Function //------------------------------------- // Constructor // Parameter: Table Name, maximum number of rows, reference fields by PAGE, number of page numbers displayed on each page Function TOracleViewPage ($ TB, $ ML, $ id ){ Global $ offset; $ This-> Table = $ TB; $ This-> MaxLine = $ ML; $ This-> Id = $ id; $ This-> StartRec = 0; If (isset ($ offset) $ this-> Offset = $ offset; Else $ this-> Offset = 0; $ This-> Condition = ""; $ This-> TheFirstPage = NULL; $ This-> PageQury = NULL; } // Initialization // Parameter: User Name, password, database Function InitDB ($ user, $ password, $ db ){ If (PHP_ OS = "WINNT") $ dllid = dl ("php3_oci80.dll "); $ This-> LinkId = OCILogon ($ user, $ password, $ db ); } // Disconnect Function Destroy (){ OCILogoff ($ this-> LinkId ); } //------------------------- // Set function //------------------------- // Set display conditions // For example, where id = '$ id' order by id desc // It must be a string that complies with the SQL syntax (this string will be added after the SQL statement) Function SetCondition ($ s ){ $ This-> Condition = $ s; } // Set the display quantity for each group Function SetNumGroup ($ pg ){ $ This-> PGroup = $ pg; } // Set the homepage. If not, the homepage is NULL. Function SetFirstPage ($ fn ){ $ This-> TheFirstPage = $ fn; } // Set the start record. If no record exists, the default value is 0. Function SetStartRecord ($ org ){ $ This-> StartRec = $ org; } // Set Transfer Parameters // Key parameter name value // For example, setpagequery ("id", $ id). You can call this function multiple times if multiple parameters are to be passed. Function SetPageQuery ($ key, $ value ){ $ Tmp [key] = $ key; $ tmp [value] = $ value; $ This-> PageQuery [] = $ tmp; } //-------------------------------- // Get Function //-------------------------------- // Retrieve the total number of records Function GetTotalRec (){ $ SQL = "SELECT Count (*) AS total FROM". $ this-> Table. "". $ this-> Condition; $ Stmt = OCIParse ($ this-> LinkId, $ SQL ); $ Bool = OCIExecute ($ stmt ); If (! $ Bool ){ Echo "connection failed! "; OCILogoff ($ this-> LinkId ); Exit; } Else { OCIFetch ($ stmt ); $ This-> Total = OCIResult ($ stmt, 1 ); } OCIFreeStatement ($ stmt ); } // Retrieve the total number of pages and the current page Function GetPage (){ $ This-> TPages = ceil ($ this-> Total/$ this-> MaxLine ); $ This-> CPages = ceil ($ this-> Offset/$ this-> MaxLine) + 1; } // Obtain the total number of groups and the current group Function GetGroup (){ $ This-> TGroup = ceil ($ this-> TPages/$ this-> PGroup ); $ This-> CGroup = ceil ($ this-> CPages/$ this-> PGroup ); } //-------------------------------- // Work functions //-------------------------------- // Read records // Main working function, which reads the corresponding records from the table based on the given conditions // The returned value is a two-dimensional array. Result [Record Number] [field name] Function ReadList (){ $ SQL = "SELECT * FROM". $ this-> Table. "". $ this-> Condition. "ORDER BY". $ this-> Id. "DESC "; $ Stmt = OCIParse ($ this-> LinkId, $ SQL ); $ Bool = OCIExecute ($ stmt ); If (! $ Bool ){ Echo "connection failed! "; OCILogoff ($ this-> LinkId ); Exit; } Else { $ Ncols = OCINumCols ($ stmt ); For ($ I = 1; $ I <= $ ncols; $ I ++) $ Column_name [$ I] = OCIColumnName ($ stmt, $ I ); $ K = 0; For ($ j = 0; $ j <$ this-> StartRec + $ this-> Offset; $ j ++) OCIFetch ($ stmt ); For ($ j = 0; $ j <$ this-> MaxLine; $ j ++ ){ If (OCIFetch ($ stmt )){ $ K ++; For ($ I = 1; $ I <= $ ncols; $ I ++) $ Temp [$ column_name [$ I] = OCIResult ($ stmt, $ I ); $ This-> Result [] = $ temp; } Else break; } $ This-> Number = $ k; } OCIFreeStatement ($ stmt ); Return $ this-> Result; } // Read the latest record // Topnum specifies the number of records to read Function ReadTopList ($ topnum ){ $ SQL = "SELECT * FROM". $ this-> Table. "". $ this-> Condition. "ORDER BY". $ this-> Id. "DESC "; $ Stmt = OCIParse ($ this-> LinkId, $ SQL ); $ Bool = OCIExecute ($ stmt ); If (! $ Bool ){ Echo "connection failed! "; OCILogoff ($ this-> LinkId ); Exit; } Else { $ Ncols = OCINumCols ($ stmt ); For ($ I = 1; $ I <= $ ncols; $ I ++) $ Column_name [$ I] = OCIColumnName ($ stmt, $ I ); $ K = 0; For ($ j = 0; $ j <$ topnum; $ j ++ ){ If (OCIFetch ($ stmt )){ $ K ++; For ($ I = 1; $ I <= $ ncols; $ I ++) $ Temp [$ column_name [$ I] = OCIResult ($ stmt, $ I ); $ This-> TopResult [] = $ temp; } Else break; } $ This-> TopNumber = $ k; } OCIFreeStatement ($ stmt ); Return $ this-> TopResult; } //--------------------------- // Paging related //--------------------------- // Display the current page and the total number of pages // This function is called after GetPage. Function ThePage (){ Echo "no.". $ this-> CPages. "Page/total". $ this-> TPages. "Page "; } // Display the flip button // This function must be called after the GetPage () function // Display the next and last pages, and add the parameters to be passed Function Page (){ $ K = count ($ this-> PageQuery ); $ StrQuery = ""; // generate a parameter string to be passed For ($ I = 0; $ I <$ k; $ I ++ ){ $ StrQuery. = "&". $ this-> PageQuery [$ I] [key]. "=". $ this-> PageQuery [$ I] [value]; } Return $ strQuery; } Function PrePage ($ strQuery ){ $ Prev = $ this-> Offset-$ this-> MaxLine; If ($ prev> = 0) Echo "<A href = $ PHP_SELF? Offset = ". $ prev. $ strQuery." class = newslink> previous page </A> "; Else if ($ this-> TheFirstPage! = NULL) Echo "<A href =". $ this-> TheFirstPage. "class = newslink> previous page </A> "; Else echo "Previous Page "; } Function NexPage ($ strQuery ){ $ Next = $ this-> Offset + $ this-> MaxLine; $ K = $ this-> Total-$ this-> StartRec; If ($ next <$ k) Echo "<A href = $ PHP_SELF? Offset = ". $ next. $ strQuery." class = newslink> next page </A> "; Else Echo "next page "; } //------------------------------------ // Record group //---------------------------------- // Display group Function NumPage (){ $ First = ($ this-> CGroup-1) * ($ this-> PGroup) + 1; $ Last = ($ first + $ this-> PGroup> $ this-> TPages )? ($ This-> TPages + 1) :( $ first + $ this-> PGroup ); $ Pr = ($ this-> CGroup-2> = 0 )? ($ This-> CGroup-2) * ($ this-> PGroup) + 1) :(-1 ); $ Prev = ($ pr! =-1 )? ($ Pr-1) * $ this-> MaxLine) :( 0 ); $ Ne = ($ this-> CGroup * $ this-> PGroup + 1 <= $ this-> TPages )? ($ This-> CGroup * $ this-> PGroup + 1) :(-1 ); $ Next = ($ ne! =-1 )? ($ Ne-1) * $ this-> MaxLine) :( 0 ); $ K = count ($ this-> PageQuery ); $ StrQuery = ""; // generate a parameter string to be passed For ($ I = 0; $ I <$ k; $ I ++ ){ $ StrQuery. = "&". $ this-> PageQuery [$ I] [key]. "=". $ this-> PageQuery [$ I] [value]; } If ($ first! = 1) Echo "<A href = $ PHP_SELF? Offset = ". $ prev. $ strQuery."> </a> "; For ($ I = $ first; $ I <$ last; $ I ++ ){ If ($ this-> CPages! = $ I ){ $ Current = ($ i-1) * $ this-> MaxLine; Echo "<A href = $ PHP_SELF? Offset = ". $ current. $ strQuery."> ". $ I." </a> "; } Else echo "<font color = # e00729>". $ I. "</font> "; } If ($ ne! =-1) Echo "<A href = $ PHP_SELF? Offset = ". $ next. $ strQuery." >></a> "; } // ***** End class } ?>

There are no problems with paging several thousand or tens of thousands of records above, but it won't work if tens of millions of data is reached. Some Efficient paging instances are organized below.

 

The following paging SQL statements are common. SQL Server also has a version that uses the TOP keyword. Remember how to use rownum between minValue and maxValue when I first learned oracle. Like the original principle of doubt, rownum is generated during the query process. Therefore, the following SQL statements are used to find 5300 rows, then the first 5000 rows are discarded, and the last 300 rows are returned. Of course, this has taken a big step, and the data returned by the database is smaller, but when the number of pages to be queried is large, there is still a waste of queries.

The Code is as follows:	Copy code
Select * From (select a. *, rownum as rnum From (select * from yz_bingrenyz) Where rownum< = 5300) Where rnum> = 5000

Linq provides Skip and Take APIs for paging. Because Entity Framework is used, you can use EFProfiler to view the generated SQL statements with curiosity. This mainly refers to the use of analysis functions such as row_numer () over (), where you can directly find the starting point of the 5,000th rows, and then retrieve 30 rows.

The Code is as follows:	Copy code
Select * From (select * From (select t. *, row_number () OVER (order by null) AS "row_number" From yz_bingrenyz t) p Where p. "row_number"> 5000) q Where rownum <= 300

It takes 1.3 s for the local machine to test the former, and 0.25 s for the latter. The difference can also be seen from the following execution plans.

If you want to write such SQL statements for each query, it is certainly troublesome. You can use stored procedures for encapsulation. However, to dynamically Execute SQL statements, the efficiency must be reduced. Therefore, in ASP. it is better to use C # To encapsulate functions in. NET, and use ADO for those that do not use the object framework..