Oracle Database Paging Query Instance summary

Oracle Paging query statements can basically 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 <=) where RN >= 21

The most inner query select * FROM table_name represents the original query statement without paging. RowNum <= 40 and RN >= 21 control the scope of each page of the paging query.

The paging query statement given above is highly efficient in most cases. The purpose of paging is to control the size of the output result set and return the result as quickly as possible. In the paging query above, this consideration is mainly reflected in the sentence where RowNum <= 40.

There are two ways to select the 21st to 40th record, one of which is shown in the above example to control the maximum by RowNum <= 40来 in the second layer of the query, which controls the minimum value at the outermost level of the query. The other way is to remove the query's second-level where rownum <= 40 statement, which controls the minimum and maximum paging values at the outermost edge of the query. This is the query statement as follows:

The code is as follows	Copy Code
SELECT * FROM (select A.*, RowNum RN From (SELECT * from table_name) A) WHERE RN BETWEEN and 40

In contrast to both of these, the first query is much more efficient than the second one in most cases.

This is because in the CBO optimization mode, Oracle can push the outer query criteria into the inner query to improve the efficiency of the inner query execution. For the first query, the second-level query condition where rownum <= 40 can be pushed into an inner query by Oracle, so that once the results of the Oracle query exceed the rownum limit, the query terminates the result.

The second query, because the query conditions between and 40 are present in the third layer of the query, and Oracle cannot push the third-tier query condition to the very inside (even if it doesn't make sense to push to the inner layer, because the most inner query doesn't know what the RN represents). Therefore, for the second query statement, the Oracle is returned to the middle tier with all the data that satisfies the condition, and the middle tier returns to the outermost and all data. The filtering of data is done at the outermost layer, which is obviously much less efficient than the first query.

The query that is analyzed above is not just a simple query for a single table, but is as effective for the most inner query as a complex multiple-table union query or for the most inner-level query that contains the sort.

There is no description of the query that contains the sort, and the next article is illustrated with an example. Here's a brief discussion of multiple-table syndication. For the most common table of magnitude join queries, the CBO may typically use two ways to connect nested loop and hash join (the efficiency of MERGE join is less efficient than hash join, which is not considered by the general CBO). Here, because paging is used, a maximum number of records is specified, and the NESTED loop can stop immediately and return the result to the middle tier when the number of records exceeds the maximum, and the hash join must process all the result sets (the MERGE join also). In most cases, it is more efficient to select the nested loop as a query connection method for paging queries (most of the time when paging queries are the data from the previous pages, the more the number of pages accessed later).

Therefore, if you don't mind using hint in your system, you can rewrite the paging query to:

The code is as follows	Copy Code
Select/*+ first_rows * * FROM (select A.*, rownum rn from (SELECT * FROM table_name) A where rownum <=) where RN >= 21

So I wrote an example with PHP.

The code is as follows

Copy Code

? Php /********************************************* Toracleviewpage v 2.0 Date: 2000-9-23 Pagination displays classes for Oracle database records Renew Date: 2000-10-19 Increases the ability to display Toprecord, allowing the first page to display a different number of records than other pages. Author: sharetop Email:ycshowtop@21cn.com ***********************************************/ Class Toracleviewpage { var $Table; Table name var $MaxLine; Show number of rows per page var $LinkId; Database connection number var $Id; Sort Reference Field var $Offset; Record offset var $Total; Total Records var $Number; Number of records read on this page The number of records actually taken out when a new record was read by Var $TopNumber; var $Result; read out the results var $TopResult//Results when reading a new record var $TheFirstPage;//special Specifies the link for the first page var $StartRec; Specify the starting record number for the second page var $TPages; Total pages var $CPages; Current page var $TGroup; var $PGroup; Number of page numbers displayed per page var $CGroup; var $Condition; Display criteria such as: where id= ' $id ' ORDER by id DESC var $PageQuery; Pagination shows the parameters to pass //------------------------------------- The following constructors, destructors, and initialization functions //------------------------------------- Constructors Parameters: Table name, maximum number of rows, paged Reference field, page number per 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; } Class Parameters: 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 //------------------------- Setting Display conditions such as: where id= ' $id ' ORDER by id DESC The requirement is a string that conforms to the SQL syntax (this string will be added to the SQL statement) function Setcondition ($s) { $this->condition= $s; } Set the number of displays per group function Setnumgroup ($PG) { $this->pgroup= $pg; } Set the first page, if none is null function Setfirstpage ($FN) { $this->thefirstpage= $FN; } Set start record, if none, default 0 function Setstartrecord ($org) { $this->startrec= $org; } Setting Pass Parameters Value of key parameter name value such as: Setpagequery ("id", $id), if you have more than one parameter to pass, you can call this function multiple times. function Setpagequery ($key, $value) { $tmp [key]= $key; $tmp [value]= $value; $this->pagequery[]= $tmp; } //-------------------------------- Get function //-------------------------------- Total number of records taken 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); } Take total number of pages, current page function GetPage () { $this->tpages=ceil ($this->total/$this->maxline); $this->cpages=ceil ($this->offset/$this->maxline) +1; } Take total group number, current group function Getgroup () { $this->tgroup=ceil ($this->tpages/$this->pgroup); $this->cgroup=ceil ($this->cpages/$this->pgroup); } //-------------------------------- Work functions //-------------------------------- Reading Records Primary work function, reading the corresponding record from the table according to the conditions given The return 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 records Topnum specify 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; } //--------------------------- Page-related //--------------------------- Show current page and total pages This function is called after GetPage (). Function thepage () { echo "->cpages". $this. " Page/Total ". $this->tpages." Page "; } Show Page Buttons This function is called after the GetPage () function Displays the next page, the top page, and the parameters to pass function Page () { $k =count ($this->pagequery); $strQuery = ""; Generate a string to pass the parameter number 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> prev </a>"; else if ($this->thefirstpage!=null) echo "<a Href=". $this->thefirstpage. "Class=newslink> prev </a>"; else echo "Prev"; } 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 grouping //---------------------------------- Show grouping 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 string to pass the parameter number 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 } ?>

Page thousands of or tens of thousands of of the above no problem but in tens of millions of data is not, the following collation of some efficient paging instances

The following paging SQL is more common, in SQL Server also has the corresponding use of the top keyword version, remember just learn to oralce when thinking about how not to rownum between MinValue and MaxValue usage. As with the original puzzle principle, the rownum is generated during the query process, so the following SQL actually detects 5300 rows, then throws out the first 5000 lines and returns the 300 lines that follow. Of course, this has entered a big step, the data returned by the database become less, but when the number of pages in the query is larger, the query or there is a certain waste.

The code is as follows	Copy Code
SELECT * From (select A.*, rownum as Rnum From (SELECT * from Yz_bingrenyz) a where RowNum <=5300) where Rnum >= 5000

       LINQ provides a skip and take API that can be used for paging, because the entity Framework is used, With curiosity, you can use Efprofiler to view the generated SQL before you know it is better to do the following pagination. The main is to use the Row_numer () over () such an analytic function, you can directly find the No. 5000 line to start the place, and then take out 30 lines on the line.

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

This machine tests the former 1.3s, the latter only 0.25s, from the following implementation plan can also see the difference.

If you want to write this SQL every time the query will be more trouble, you can use stored procedures for encapsulation, but because the dynamic execution of SQL, the efficiency must be compromised, so in the ASP.net with C # encapsulation function is better, for not using the Entity Framework with ado.net, incoming table name, The primary key name, number of pages, and rows to take as arguments, with DbCommand to perform the return result.