oracle数据库分页查询实例小结

作者：袖梨 2022-06-29

Oracle的分页查询语句基本上可以按照本文给出的格式来进行套用。

分页查询格式：

代码如下	复制代码
SELECT * FROM ( SELECT A., ROWNUM RN FROM (SELECT FROM TABLE_NAME) A WHERE ROWNUM <= 40 ) WHERE RN >= 21

其中最内层的查询SELECT * FROM TABLE_NAME表示不进行翻页的原始查询语句。ROWNUM <= 40和RN >= 21控制分页查询的每页的范围。

上面给出的这个分页查询语句，在大多数情况拥有较高的效率。分页的目的就是控制输出结果集大小，将结果尽快的返回。在上面的分页查询语句中，这种考虑主要体现在WHERE ROWNUM <= 40这句上。

选择第21到40条记录存在两种方法，一种是上面例子中展示的在查询的第二层通过ROWNUM <= 40来控制最大值，在查询的最外层控制最小值。而另一种方式是去掉查询第二层的WHERE ROWNUM <= 40语句，在查询的最外层控制分页的最小值和最大值。这是，查询语句如下：

代码如下	复制代码
SELECT * FROM ( SELECT A., ROWNUM RN FROM (SELECT FROM TABLE_NAME) A ) WHERE RN BETWEEN 21 AND 40

对比这两种写法，绝大多数的情况下，第一个查询的效率比第二个高得多。

这是由于CBO优化模式下，Oracle可以将外层的查询条件推到内层查询中，以提高内层查询的执行效率。对于第一个查询语句，第二层的查询条件WHERE ROWNUM <= 40就可以被Oracle推入到内层查询中，这样Oracle查询的结果一旦超过了ROWNUM限制条件，就终止查询将结果返回了。

而第二个查询语句，由于查询条件BETWEEN 21 AND 40是存在于查询的第三层，而Oracle无法将第三层的查询条件推到最内层（即使推到最内层也没有意义，因为最内层查询不知道RN代表什么）。因此，对于第二个查询语句，Oracle最内层返回给中间层的是所有满足条件的数据，而中间层返回给最外层的也是所有数据。数据的过滤在最外层完成，显然这个效率要比第一个查询低得多。

上面分析的查询不仅仅是针对单表的简单查询，对于最内层查询是复杂的多表联合查询或最内层查询包含排序的情况一样有效。

这里就不对包含排序的查询进行说明了，下一篇文章会通过例子来详细说明。下面简单讨论一下多表联合的情况。对于最常见的等值表连接查询，CBO一般可能会采用两种连接方式NESTED LOOP和HASH JOIN（MERGE JOIN效率比HASH JOIN效率低，一般CBO不会考虑）。在这里，由于使用了分页，因此指定了一个返回的最大记录数，NESTED LOOP在返回记录数超过最大值时可以马上停止并将结果返回给中间层，而HASH JOIN必须处理完所有结果集（MERGE JOIN也是）。那么在大部分的情况下，对于分页查询选择NESTED LOOP作为查询的连接方法具有较高的效率（分页查询的时候绝大部分的情况是查询前几页的数据，越靠后面的页数访问几率越小）。

因此，如果不介意在系统中使用HINT的话，可以将分页的查询语句改写为：

代码如下	复制代码
SELECT /+ FIRST_ROWS / * FROM ( SELECT A., ROWNUM RN FROM (SELECT FROM TABLE_NAME) A WHERE ROWNUM <= 40 ) WHERE RN >= 21

于是和php写了一个实例

代码如下

复制代码

＜?PHP
/*********************************************
TOracleViewPage　v 2.0
日期：2000-9-23
分页显示Oracle数据库记录的类
更新日期：2000-10-19
增加显示TopRecord的功能，允许第一页显示的记录数与其它页不同。
作者：sharetop
email:[email protected]
***********************************************/
class TOracleViewPage {
var $Table; //表名
var $MaxLine; //每页显示行数
var $LinkId; //数据库连接号
var $Id; //排序参考字段
var $Offset; //记录偏移量
var $Total; //记录总数
var $Number; //本页读取的记录数
var $TopNumber;//读新记录时实际取出的记录数
var $Result; //读出的结果
var $TopResult;//读新记录时的结果
var $TheFirstPage;//特殊指定第一页的链接
var $StartRec; //指定第二页的起始记录号
var $TPages; //总页数
var $CPages; //当前页数
var $TGroup;
var $PGroup; //每页显示的页号个数
var $CGroup;
var $Condition; //显示条件如：where id='$id' order by id desc
var $PageQuery; //分页显示要传递的参数
//-------------------------------------
// 以下构造函数、析构函数及初始化函数
//-------------------------------------
//构造函数
//参数：表名、最大行数、分页参考的字段、每页显示的页号数
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;
}
//初始化
//参数：用户名、密码、数据库
function InitDB($user,$password,$db){
if (PHP_OS == "WINNT") $dllid=dl("php3_oci80.dll");
$this->LinkId = OCILogon($user,$password,$db);
}
//断开
function Destroy(){
OCILogoff($this->LinkId);
}
//-------------------------
// Set 函数
//-------------------------
//设置显示条件
//如：where id='$id' order by id desc
//要求是字串，符合SQL语法（本字串将加在SQL语句后）
function SetCondition($s){
$this->Condition=$s;
}
//设置每组的显示个数
function SetNumGroup($pg){
$this->PGroup=$pg;
}
//设置首页，如无则为NULL
function SetFirstPage($fn){
$this->TheFirstPage=$fn;
}
//设置起始记录，如无则取默认0
function SetStartRecord($org){
$this->StartRec=$org;
}
//设置传递参数
// key参数名 value参数值
// 如：setpagequery("id",$id);如有多个参数要传递，可多次调用本函数。
function SetPageQuery($key,$value){
$tmp[key]=$key; $tmp[value]=$value;
$this->PageQuery[]=$tmp;
}
//--------------------------------
// Get 函数
//--------------------------------
//取记录总数
function GetTotalRec(){
$SQL="SELECT Count(*) AS total FROM ".$this->Table." ".$this->Condition;
$stmt = OCIParse($this->LinkId,$SQL);
$bool = OCIExecute($stmt);
if (!$bool) {
echo "连接失败！";
OCILogoff($this->LinkId);
exit;
}
else {
OCIFetch($stmt);
$this->Total=OCIResult($stmt,1);
}
OCIFreeStatement($stmt);
}
//取总页数、当前页
function GetPage(){
$this->TPages=ceil($this->Total/$this->MaxLine);
$this->CPages=ceil($this->Offset/$this->MaxLine)+1;
}
//取总组数、当前组
function GetGroup() {
$this->TGroup=ceil($this->TPages/$this->PGroup);
$this->CGroup=ceil($this->CPages/$this->PGroup);
}
//--------------------------------
// 工作函数
//--------------------------------
//读取记录
//　主要工作函数，根据所给的条件从表中读取相应的记录
//　返回值是一个二维数组，Result[记录号][字段名]
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 "连接失败！";
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;
}
//读最新的记录
//topnum指定要读出的记录数
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 "连接失败！";
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;
}
//---------------------------
// 分页相关
//---------------------------
//显示当前页及总页数
//本函数在GetPage()后调用。
function ThePage() {
echo "第".$this->CPages."页/共".$this->TPages."页";
}
//显示翻页按钮
//此函数要在GetPage()函数之后调用
//显示下页、上页，并加上要传递的参数
function Page() {
$k=count($this->PageQuery);
$strQuery=""; //生成一个要传递参数字串
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>上一页＜/A>";
else if($this->TheFirstPage!=NULL)
echo "＜A href=".$this->TheFirstPage." class=newslink>上一页＜/A>";
else echo "上一页";
}
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>下一页＜/A>";
else
echo "下一页";
}
//------------------------------------
// 记录分组
//----------------------------------
//显示分组
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=""; //生成一个要传递参数字串
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
}
?>

上面分页几千或几万条记录没有问题但是到了千万数据就不行了，下面整理了一些高效分页实例

以下的分页SQL比较常见的，在SQL Server也有对应的使用TOP关键字的版本，记得刚学Oralce的时候就想着怎么不能rownum between minValue and maxValue的用法。与最初的疑惑的原理一样，rownum是在查询过程中生成的，因此以下的SQL其实是查出来5300行，然后扔掉了前面5000行，返回后面的300行。当然这种已经进了一大步的，由数据库返回的数据变少的，只是当查询的页数比较大的时候，查询还是存在一定的浪费。

代码如下	复制代码
select * from (select a., rownum as rnum from (select from yz_bingrenyz) a where rownum <=5300) where rnum >= 5000

Linq提供了Skip和Take的API可以用于分页，由于使用的是Entity Framework，在好奇的驱使下用EFProfiler查看生成的SQL，才知道这样以下分页更好。主要就是使用了row_numer()over()这样的分析函数，可以直接找到那第5000行开始的地方，然后在取出30行就行了。

代码如下	复制代码
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

本机测试前者耗时1.3s,后者仅0.25s，从以下的执行计划也能看出差异来。

如果每次查询都要写这种SQL那肯定比较麻烦，可以采用存储过程进行封装，但由于要动态执行SQL，效率肯定又要打折扣，所以在ASP.NET中用C#封装函数比较好，对于没有使用实体框架的而用ADO.NET的，传入表名、主键名、页数、要取的行数作为参数，用DBCommand进行执行返回结果即可。