`
qepwqnp
  • 浏览: 114708 次
  • 性别: Icon_minigender_1
  • 来自: 成都
社区版块
存档分类
最新评论

数据库分页大全(oracle利用解析函数row_number高效分页)

阅读更多

数据库分页大全(oracle利用解析函数row_number高效分页)

 

Mysql分页采用limt关键字

select * from t_order limit 5,10; #返回第6-15行数据
select
 * from  t_order limit  5; #返回前5
select
 * from  t_order limit  0,5; #返回前5

Mssql 2000分页采用top关键字(20005以上版本也支持关键字rownum)

Select top 10 * from t_order where id not in (select id from t_order where id>5 ); //返回第615行数据

其中10表示取10记录 5表示从第5条记录开始取

Oracle分页

采用rownum关键字(三层嵌套)

SELECT * FROM(
  SELECT A.*,ROWNUM  num FROM
  (SELECT * FROM t_order)A
  WHERE
  ROWNUM<=
15)
WHERE num>=
5;--返回第5-15行数据

采用row_number解析函数进行分页(效率更高)

SELECT xx.* FROM(
SELECT t.*,row_number() over(ORDER BY o_id)AS num
FROM t_order t
)xx
WHERE num BETWEEN
5 AND 15;

--返回第5-15行数据

解析函数能用格式

函数() over(pertion by 字段 order by 字段);

Pertion 按照某个字段分区

Order 按照勒个字段排序

分享到:
评论
15 楼 sp42 2016-04-30  
Oracle 不是很吊的么,怎么连个分页都这么麻烦?
14 楼 metarnetyflu 2014-10-16  
不知道哪里快了!!!over()是最慢的,rownum其次,rowid最快!
13 楼 wfd0807 2014-09-29  
09年发的博文,五年了,一定影响到了不少人。
首先,你描述的三层嵌套就比oracle官方介绍中少了一个关键信息,这个信息在大部分数据库系统的分页查询中都是必须的,那就是原始结果集排序,而且排序的条件很苛刻,就是排序列的组合必须唯一,否则有很大的可能出现分页查询的错误,即相邻页出现相同的记录,同时有部分记录在所有页面都不显示的问题。单表查询、小数据量查询的时候,很少会出现,当多表、大数据量查询的时候,不排序分页就会出问题。
另外,用分析函数分页是效率是最低的,我不知道博主怎么说效率高的?随便比较一下执行计划,就发现两者在效率方面没有可比性。看不懂执行计划的,用三万条记录实验一下,比较一下两者的查询消耗的时间,明显会发现分析函数耗费时间多。
rownum是伪列,row_Number()是分析函数,无论从实现方式,还是作用目的,在分页上后者都不可能比前者效率高!
后来者,慎重参考!
12 楼 641216927 2009-12-22  
①SELECT * FROM(
  SELECT A.FIELD_ID,ROWNUM  num FROM
  (SELECT * FROM recordtable order by FIELD_ID) A
  WHERE
  ROWNUM<=1550)
WHERE num>=5
num是有序的5~1550
②select * from
(select t.FIELD_ID,ROWNUM num from recordtable t where ROWNUM<=1550 order by FIELD_ID)
where num>=5
num是无序的,乱的
但是②比①的速度要快的多!
11 楼 rabbitbug 2009-12-18  
如果没有排序,两层也是可以的
但一有排序,两层是不对地,需要再加一层
你可以试试

zfc827 写道
蔡华江 写道
cnlinkin 写道
用rownum两层就可以了呀?
select * from
(select t.*,rownum r from mytable t where rownum<=10)
where r>=5

你确定两层没有问题?


有什么问题?

10 楼 zfc827 2009-11-26  
蔡华江 写道
cnlinkin 写道
用rownum两层就可以了呀?
select * from
(select t.*,rownum r from mytable t where rownum<=10)
where r>=5

你确定两层没有问题?


有什么问题?
9 楼 czllfy 2009-11-21  
经测试发现:采用rownum方式进行分页,越后面越慢,不知道为什么,对于几千万的数据定位到最后的100条数据,受不了
8 楼 mydu 2009-11-20  
不错,mysql最方便
7 楼 蔡华江 2009-11-19  
cnlinkin 写道
用rownum两层就可以了呀?
select * from
(select t.*,rownum r from mytable t where rownum<=10)
where r>=5

你确定两层没有问题?
6 楼 hhxlyl 2009-11-19  
事实证明:
select * from
(select t.*,rownum r from mytable t where rownum<=15)
where r>=5
效率最高
5 楼 cnlinkin 2009-11-14  
用rownum两层就可以了呀?
select * from
(select t.*,rownum r from mytable t where rownum<=10)
where r>=5
4 楼 czllfy 2009-11-14  
<div class="quote_title">qepwqnp 写道</div>
<div class="quote_div">
<p class="MsoNormal" style="margin: 0cm 0cm 0pt;"><span style="font-size: small;"><span>数据库分页大全(</span><span style="color: #000000;"><span style="font-family: Times New Roman;">oracle</span></span><span>利用解析函数</span><span style="color: #000000;"><span style="font-family: Times New Roman;">row_number</span></span><span>高效分页)</span></span></p>
<p class="MsoNormal" style="margin: 0cm 0cm 0pt;"><span style="color: #000000;"><span style="font-size: small; font-family: Times New Roman;"> </span></span></p>
<p class="MsoNormal" style="margin: 0cm 0cm 0pt;"><span style="font-size: small;"><span style="color: #ff0000;"><span style="font-family: Times New Roman;">Mysql</span></span><span>分页采用</span><span style="color: #ff0000;"><span style="font-family: Times New Roman;">limt</span></span><span>关键字</span></span></p>
<p class="MsoNormal" style="margin: 0cm 0cm 0pt;"><strong><span lang="EN-US">select</span></strong><strong><span lang="EN-US"><span style="font-family: Times New Roman;"> </span></span></strong><strong><span lang="EN-US">*</span></strong><strong><span lang="EN-US"><span style="font-family: Times New Roman;"> </span></span></strong><strong><span lang="EN-US">from</span></strong><strong><span lang="EN-US"><span style="font-family: Times New Roman;"> </span></span></strong><strong><span lang="EN-US">t_order</span></strong><strong><span lang="EN-US"><span style="font-family: Times New Roman;"> </span></span></strong><strong><span lang="EN-US">limit</span></strong><strong><span lang="EN-US"><span style="font-family: Times New Roman;"> </span></span></strong><strong><span lang="EN-US">5,</span></strong><strong><span lang="EN-US">10</span></strong><strong><span lang="EN-US">;</span></strong><span style="font-family: Times New Roman;"><strong><span lang="EN-US"> </span></strong><span style="color: #000000;"><span style="font-size: small;">#</span></span></span><span style="font-size: small;"><span>返回第</span><span style="color: #000000;"><span style="font-family: Times New Roman;">6-15</span></span><span>行数据</span><span style="color: #000000;"><span style="font-family: Times New Roman;"> </span></span></span><strong><span lang="EN-US"><br>select</span></strong><strong><span lang="EN-US"><span style="font-family: Times New Roman;"> </span></span></strong><strong><span lang="EN-US">*</span></strong><strong><span lang="EN-US"><span style="font-family: Times New Roman;"> </span></span></strong><strong><span lang="EN-US">from</span></strong><strong><span lang="EN-US"><span style="font-family: Times New Roman;">  </span></span></strong><strong><span lang="EN-US">t_order limit</span></strong><strong><span lang="EN-US"><span style="font-family: Times New Roman;"> </span></span></strong><strong><span lang="EN-US"> 5</span></strong><strong><span lang="EN-US">;</span></strong><span style="font-size: small;"><span style="color: #000000;"><span style="font-family: Times New Roman;"> #</span></span><span>返回前</span><span style="color: #000000;"><span style="font-family: Times New Roman;">5</span></span><span>行</span><span style="color: #000000;"><span style="font-family: Times New Roman;"> </span></span></span><strong><span lang="EN-US"><br>select</span></strong><strong><span lang="EN-US"><span style="font-family: Times New Roman;"> </span></span></strong><strong><span lang="EN-US">*</span></strong><strong><span lang="EN-US"><span style="font-family: Times New Roman;"> </span></span></strong><strong><span lang="EN-US">from</span></strong><strong><span lang="EN-US"><span style="font-family: Times New Roman;">  </span></span></strong><strong><span lang="EN-US">t_order limit</span></strong><strong><span lang="EN-US"><span style="font-family: Times New Roman;"> </span></span></strong><strong><span lang="EN-US"> </span></strong><strong><span lang="EN-US">0,5</span></strong><strong><span lang="EN-US">;</span></strong><span style="font-family: Times New Roman;"><strong><span lang="EN-US"> </span></strong><span style="color: #000000;"><span style="font-size: small;">#</span></span></span><span style="font-size: small;"><span>返回前</span><span style="color: #000000;"><span style="font-family: Times New Roman;">5</span></span><span>行</span><strong></strong></span></p>
<p class="MsoNormal" style="margin: 0cm 0cm 0pt;"><span style="font-size: small;"><span style="color: #ff0000;"><span style="font-family: Times New Roman;">Mssql 2000</span></span><span>分页采用</span><span style="color: #ff0000;"><span style="font-family: Times New Roman;">top</span></span><span>关键字</span><span style="color: #ff0000;"><span style="font-family: Times New Roman;">(20005</span></span><span>以上版本也支持关键字</span><span style="color: #ff0000;"><span style="font-family: Times New Roman;">rownum)</span></span></span></p>
<p class="MsoNormal" style="margin: 0cm 0cm 0pt;"><strong><span lang="EN-US">Select top </span></strong><strong><span lang="EN-US">10</span></strong><strong><span lang="EN-US"> * from t_order where id not in (select id from t_order where id&gt;</span></strong><strong><span lang="EN-US">5</span></strong><strong><span lang="EN-US"> )</span></strong><span style="font-size: small;"><span style="color: #000000;"><span style="font-family: Times New Roman;">; //</span></span><span>返回第</span><span style="color: #000000;"><span style="font-family: Times New Roman;">6</span></span><span>到</span><span style="color: #000000;"><span style="font-family: Times New Roman;">15</span></span><span>行数据</span></span></p>
<p class="MsoNormal" style="margin: 0cm 0cm 0pt;"><span style="font-size: small;"><span>其中</span><span style="color: #000000;"><span style="font-family: Times New Roman;">10</span></span><span>表示取</span><span style="color: #000000;"><span style="font-family: Times New Roman;">10</span></span><span>记录</span><span style="color: #000000;"><span style="font-family: Times New Roman;"> 5</span></span><span>表示从第</span><span style="color: #000000;"><span style="font-family: Times New Roman;">5</span></span><span>条记录开始取</span></span></p>
<p class="MsoNormal" style="margin: 0cm 0cm 0pt;"><span style="font-size: small;"><span style="color: #ff0000;"><span style="font-family: Times New Roman;">Oracle</span></span><span>分页</span></span></p>
<p class="MsoNormal" style="margin: 0cm 0cm 0pt;"><span style="font-size: small;"><span>①</span><span>采用</span><span style="color: #ff0000;"><span style="font-family: Times New Roman;">rownum</span></span><span>关键字</span><span style="color: #ff0000;"><span style="font-family: Times New Roman;">(</span></span><span>三层嵌套</span><span style="color: #ff0000;"><span style="font-family: Times New Roman;">)</span></span></span></p>
<p class="MsoNormal" style="margin: 0cm 0cm 0pt;"><strong><span lang="EN-US">SELECT</span></strong><span lang="EN-US"> * <strong>FROM</strong>(<br><span>  </span><strong>SELECT</strong> A.*,<strong>ROWNUM</strong><span>  </span>num <strong>FROM</strong> <br><span>  </span>(<strong>SELECT</strong> * <strong>FROM</strong> t_order)A<br><span>  </span><strong>WHERE</strong><br><span>  </span><strong>ROWNUM</strong>&lt;=</span><span lang="EN-US">15</span><span lang="EN-US">)<br><strong>WHERE</strong> num&gt;=</span><span lang="EN-US">5</span><span lang="EN-US">;<strong>-</strong></span><span style="font-size: small;"><span style="color: #000000;"><span style="font-family: Times New Roman;">-</span></span><span>返回第</span><span style="color: #000000;"><span style="font-family: Times New Roman;">5-15</span></span><span>行数据</span></span></p>
<p class="MsoNormal" style="margin: 0cm 0cm 0pt;"><span style="font-size: small;"><span>②</span><span>采用</span><span style="color: #ff0000;"><span style="font-family: Times New Roman;">row_number</span></span><span>解析函数进行分页</span><span style="color: #ff0000;"><span style="font-family: Times New Roman;">(</span></span><span>效率更高</span><span style="font-family: Times New Roman;"><span style="color: #ff0000;">)</span></span></span></p>
<p class="MsoNormal" style="margin: 0cm 0cm 0pt;"><strong><span lang="EN-US">SELECT</span></strong><span lang="EN-US"> xx.* <strong>FROM</strong>(<br><strong>SELECT</strong> t.*,row_number() over(<strong>ORDER</strong> <strong>BY</strong> o_id)<strong>AS</strong> num<br><strong>FROM</strong> t_order t<br>)xx<br><strong>WHERE</strong> num <strong>BETWEEN</strong> </span><span lang="EN-US">5</span><span lang="EN-US"> <strong>AND</strong> </span><span lang="EN-US">15</span><span lang="EN-US">;</span></p>
<p class="MsoNormal" style="margin: 0cm 0cm 0pt;"><span style="font-size: small;"><span style="color: #000000;"><span style="font-family: Times New Roman;">--</span></span><span>返回第</span><span style="color: #000000;"><span style="font-family: Times New Roman;">5-15</span></span><span>行数据</span></span></p>
<p class="MsoNormal" style="margin: 0cm 0cm 0pt;"><span style="font-size: small;"><span>解析函数能用格式</span></span></p>
<p class="MsoNormal" style="margin: 0cm 0cm 0pt;"><span style="font-size: small;"><span>函数</span><span style="color: #000000;"><span style="font-family: Times New Roman;">() over(pertion by </span></span><span>字段</span><span style="color: #000000;"><span style="font-family: Times New Roman;"> order by </span></span><span>字段</span><span style="color: #000000;"><span style="font-family: Times New Roman;">);</span></span></span></p>
<p class="MsoNormal" style="margin: 0cm 0cm 0pt;"><span style="font-size: small;"><span style="color: #000000;"><span style="font-family: Times New Roman;">Pertion </span></span><span>按照某个字段分区</span></span></p>
<p class="MsoNormal" style="margin: 0cm 0cm 0pt;"><span style="font-size: small;"><span style="color: #000000;"><span style="font-family: Times New Roman;">Order </span></span><span>按照勒个字段排序</span></span></p>
</div>
<div class="quote_div">经过对单表2134043W数据进行测试发现row_number分布耗时15S,而采用ROWNUM仅0.0062S</div>
<p> </p>
3 楼 ring09h 2009-09-06  
SQL Server 2000的Top分页 id必须有唯一性约束
2 楼 leejon 2009-09-06  
分页方面,还是mysql方便,sqlserver呢,也比较麻烦,尤其是要进行排序时,也要很长的sql。oracle确实真的麻烦。记得上次,查询一次,写了三个嵌套的select,还用到集合的操作,麻烦。直接在程序中,用hibernate的那几个方法,就会存在效率问题。分页确实是个大问题。
1 楼 json615 2009-09-03  
楼主 比较辛苦哦 我今天笔试的时候 就记错了 把mysql的分页写在Oracle上面了 简直是 日笨啊

相关推荐

Global site tag (gtag.js) - Google Analytics