- 浏览: 114708 次
- 性别:
- 来自: 成都
最新评论
-
sp42:
Oracle 不是很吊的么,怎么连个分页都这么麻烦?
数据库分页大全(oracle利用解析函数row_number高效分页) -
metarnetyflu:
不知道哪里快了!!!over()是最慢的,rownum其次,r ...
数据库分页大全(oracle利用解析函数row_number高效分页) -
wfd0807:
09年发的博文,五年了,一定影响到了不少人。首先,你描述的三层 ...
数据库分页大全(oracle利用解析函数row_number高效分页) -
diyunpeng:
学习了,写的不错。
python urlOpen使用代理 -
qepwqnp:
帖子沉的好深,涝起来
js_自己封装一个可查询frame中对象的一个方法
数据库分页大全(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 ); //返回第6到15行数据
其中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()是分析函数,无论从实现方式,还是作用目的,在分页上后者都不可能比前者效率高!
后来者,慎重参考!
首先,你描述的三层嵌套就比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是无序的,乱的
但是②比①的速度要快的多!
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
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
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
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
效率最高
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
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></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><=</span><span lang="EN-US">15</span><span lang="EN-US">)<br><strong>WHERE</strong> num>=</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>
<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></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><=</span><span lang="EN-US">15</span><span lang="EN-US">)<br><strong>WHERE</strong> num>=</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上面了 简直是 日笨啊
发表评论
-
字节顺序大端模式Big Endian
2023-12-22 16:57 1851大端模式和小端模式是描述多字节数据在存储时的字节顺序的两种方 ... -
mediawiki 如何开发特殊页面
2015-04-01 14:56 1084Mediawiki特殊页面开发步骤 1 页面显示名字设 ... -
mediawiki 实现ajax请求及demo
2015-04-01 14:36 961实现方案为使用mediawiki 的api。 1.前台 ... -
mediawiki1.24源码分析(一)
2015-04-01 14:31 0所有分析说明采用文字使用浅红色、小四号楷体。 Index ... -
Mysql ERROR 1040 (00000): Too many connections
2013-09-23 16:38 1652程序添加数据库数据:错误信息: com.mysql. ... -
解决AJAX传输到后台时中文乱码问题
2011-09-14 08:49 1778var urlPaging = "中间信息&qu ... -
Webservices手写客户端调用
2011-08-11 14:08 1297客户端调用 // 实例化IS ... -
python文章采集例子(爬取http://infoq.com)
2011-06-24 23:41 3623写了个采集http://infoq.com资源的小程序,原理: ... -
一键搞定python连接mysql驱动问题(windows版本)
2011-06-21 17:49 2355对于mysql驱动问题折腾了一下午,现共享出解决方案 1:手动 ... -
python爬虫抓站技巧
2011-06-19 20:20 2248本文章部分内容为转载----- 本人学习python中,用得最 ... -
python urlOpen使用代理
2011-06-17 17:42 9760利用urllib包 import urllib prox ... -
TypeError: 'str' object is not callable
2011-06-17 14:25 8517weburl = 'xyz' print " ... -
Eclipse 如何本地进行远程调试
2011-06-16 11:42 2001步骤:(无法上传图片) 1 确保TOMCAT在DEBUG模 ... -
js_自己封装一个可查询frame中对象的一个方法
2011-01-20 16:34 1352说明 用途: 查询当前页面以及所包含的frame中id为传入 ... -
在Eclipse中启动tomcat,并指定启动目录
2011-01-11 16:44 1460我们会有这样的需求:为了工程结构统层次清晰,在开发时不将工程定 ... -
ORACLE VARCHAR 排序问题
2010-06-24 16:31 4035为了实现varchar型的字段序号递增,我每次插入数据要先找出 ... -
SQL 连接 JOIN 例解。(左连接,右连接,全连接,内连接,交叉连接,自连接)
2009-09-09 22:11 1538SQL 连接 JOIN 例解。( ... -
利用properties资源文件追加写入,而不覆盖
2009-08-14 09:11 4900利用properties资源文件追加写入,而不覆盖 Prop ... -
Spring+AspectJ采用注释做申明式事务(手工山寨版)
2009-06-07 10:56 1062建立表 DROP TABLE t_book; CRE ... -
jdbc优化[手工原创]
2009-04-16 21:26 1260java 与oracle数据库效率优化 @java 方面 ...
相关推荐
1、资源内容地址:https://blog.csdn.net/abc6838/article/details/143720369 2、数据特点:今年全新,手工精心整理,放心引用,数据来自权威,且标注《数据来源》,相对于其他人的控制变量数据准确很多,适合写论文做实证用 ,不会出现数据造假问题 3、适用对象:大学生,本科生,研究生小白可用,容易上手!!! 4、课程引用: 经济学,地理学,城市规划与城市研究,公共政策与管理,社会学,商业与管理
CPPC++_更好的Windows字体渲染
10018
cppc++
二环北路东段欣心家园小区商业B段(中石油加油站东邻).m4a
cppc++
C2005
Python课程设计之高校教务系统
1.版本:matlab2014/2019a/2024a 2.附赠案例数据可直接运行matlab程序。 3.代码特点:参数化编程、参数可方便更改、代码编程思路清晰、注释明细。 4.适用对象:计算机,电子信息工程、数学等专业的大学生课程设计、期末大作业和毕业设计。 替换数据可以直接使用,注释清楚,适合新手
10023
使用QUndoStack撤销栈实现QTreeWidget删除item及撤销、重做
1.版本:matlab2014/2019a/2024a 2.附赠案例数据可直接运行matlab程序。 3.代码特点:参数化编程、参数可方便更改、代码编程思路清晰、注释明细。 4.适用对象:计算机,电子信息工程、数学等专业的大学生课程设计、期末大作业和毕业设计。 替换数据可以直接使用,注释清楚,适合新手
Python课程设计之飞机大战3.zip
SpringBoot分布式事务
CPPC++_tensorort for yolo系列YOLOv10YOLOv9YOLOv8YOLOv7YOLOv6Y
1.版本:matlab2014/2019a/2024a 2.附赠案例数据可直接运行matlab程序。 3.代码特点:参数化编程、参数可方便更改、代码编程思路清晰、注释明细。 4.适用对象:计算机,电子信息工程、数学等专业的大学生课程设计、期末大作业和毕业设计。 替换数据可以直接使用,注释清楚,适合新手
收录了「IT无知君」CSDN博客中涉及的Java项目源码,还有许多的开发工具类,都是我自己在用在不断维护的,需要请进!
CPPC++_更新你的CFW作弊码固件,更直接地从你的任天堂交换机
test-dadfateq