robbin对oracle
ROWNUM 方式分页效率问题的看法:
http://www.javaeye.com/topic/8805:
-
select
*from
(select
row_.*,rownumrownum_from
(......)row_where
rownum<=?)where
rownum_>?
select * from ( select row_.*, rownum rownum_ from (...... ) row_ where rownum <= ?) where rownum_ > ?
这种用嵌套3层的分页sql是处理效率最高的办法,你找不到更高效的方法了。
我在2000的时候就研究Oracle的分页问题了,研究了半年多,换了好多种办法,最后确定还是这种办法最高效。
返回有条件且经过排序的某段记录(三层结构)
url: http://www.javaeye.com/topic/186927?page=2
-
select
*
-
from
(select
*
-
from
(select
t.*,rownumnum
-
from
userinfot
-
where
siteid=73
-
order
by
regtimedesc
)
-
where
num<5)
-
where
num>11;
数据库分页大全(oracle
利用解析函数row_number
高效分页)
url: http://www.javaeye.com/topic/394708
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
关键字
(2005
以上版本也支持关键字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
;
②采用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
按照勒个字段排序
http://www.javaeye.com/topic/394708
czllfy: 发表时间:2009-11-14
经过对单表2134043W数据进行测试发现row_number分布耗时15S,而采用ROWNUM仅0.0062S
ORACLE大数据量下的分页解决方法
http://huangzhaorongit.javaeye.com/blog/241293
(一)分页实现及性能
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
(二)Oracle Top n
SELECT A.*, ROWNUM RN
FROM (SELECT * FROM TABLE_NAME) A
WHERE
ROWNUM <= 40
以上是oracle 实现top n的功能
SELECT A.*, ROWNUM RN
FROM (SELECT * FROM TABLE_NAME) A
WHERE ROWNUM between 2 and 100
总是返回空记录
原因:
对于rownum来说它是oracle系统顺序分配为从查询返回的行的编号,返回的第一行分配的是1,第二行是2,依此类推,这个伪字段可以用于限制查询返回的总行数,而且rownum不能以任何表的名称作为前缀。
举例说明:
例如表:student(学生)表,表结构为:
ID char(6) --学号
name VARCHAR2(10) --姓名
create table student (ID char(6), name
VARCHAR2(100));
insert into sale values('200001',‘张一’);
insert into sale
values('200002',‘王二’);
insert into sale values('200003',‘李三’);
insert
into sale values('200004',‘赵四’);
commit;
(1) rownum 对于等于某值的查询条件
如
果希望找到学生表中第一条学生的信息,可以使用rownum=1作为条件。但是想找到学生表中第二条学生的信息,使用rownum=2结果查不到数据。因
为rownum都是从1开始,但是1以上的自然数在rownum做等于判断是时认为都是false条件,所以无法查到rownum = n(n>1的自然数)。
SQL> select rownum,id,name from student where
rownum=1;(可以用在限制返回记录条数的地方,保证不出错,如:隐式游标)
SQL> select rownum,id,name from
student where rownum=1;
ROWNUM ID NAME
---------- ------
---------------------------------------------------
1 200001 张一
SQL> select rownum,id,name from student where rownum =2;
ROWNUM
ID NAME
---------- ------
---------------------------------------------------
(2)rownum对于大于某值的查询条件
如果想找到从第二行记录以后的记录,当使用rownum>2是查不出记录的,原因是由于rownum是一个总是从1开始的伪列,Oracle
认为rownum> n(n>1的自然数)这种条件依旧不成立,所以查不到记录
SQL> select rownum,id,name
from student where rownum >2;
ROWNUM ID NAME
---------- ------
---------------------------------------------------
那如何才能找到第二行以后的记录呀。可以使用以下的子查询方法来解决。注意子查询中的rownum必须要有别名,否则还是不会查出记录来,这是因为rownum不是某个表的列,如果不起别名的话,无法知道rownum是子查询的列还是主查询的列。
SQL>select * from(select rownum no ,id,name from student) where no>2;
NO ID NAME
---------- ------
---------------------------------------------------
3 200003 李三
4 200004 赵四
SQL> select * from(select rownum,id,name from
student)where rownum>2;
ROWNUM ID NAME
---------- ------
---------------------------------------------------
(3)rownum对于小于某值的查询条件
如果想找到第三条记录以前的记录,当使用rownum<3是能得到两条记录的。显然rownum对于rownum<n((n>1的自然数)的条件认为是成立的,所以可以找到记录。
SQL> select rownum,id,name from student where rownum <3;
ROWNUM ID NAME
---------- ------
---------------------------------------------------
1 200001 张一
2 200002 王二
综
上几种情况,可能有时候需要查询rownum在某区间的数据,那怎么办呀从上可以看出rownum对小于某值的查询条件是人为true的,rownum对
于大于某值的查询条件直接认为是false的,但是可以间接的让它转为认为是true的。那就必须使用子查询。例如要查询rownum在第二行到第三行之
间的数据,包括第二行和第三行数据,那么我们只能写以下语句,先让它返回小于等于三的记录行,然后在主查询中判断新的rownum的别名列大于等于二的记
录行。但是这样的操作会在大数据集中影响速度。
SQL> select * from (select rownum no,id,name from
student where rownum<=3 ) where no >=2;
NO ID NAME
---------- ------ ---------------------------------------------------
2 200002 王二
3 200003 李三
(4)rownum和排序
Oracle中的rownum的是在取数据的时候产生的序号,所以想对指定排序的数据去指定的rowmun行数据就必须注意了。
SQL>
select rownum ,id,name from student order by name;
ROWNUM ID NAME
---------- ------ ---------------------------------------------------
3 200003 李三
2 200002 王二
1 200001 张一
4 200004 赵四
可以看出,rownum并不是按照name列来生成的序号。系统是按照记录插入时的顺序给记录排的号,rowid也是顺序分配的。为了解决这个问题,必须使用子查询
SQL> select rownum ,id,name from (select * from student order by name);
ROWNUM ID NAME
---------- ------
---------------------------------------------------
1 200003 李三
2 200002 王二
3 200001 张一
4 200004 赵四
这样就成了按name排序,并且用rownum标出正确序号(有小到大)
---
以上为rownum的必学处,为了更好地使用rownum打下基础。
其 实, 理解rownum的关键是Oracle 如何执行查询语句.
如果先执行笛卡尔集运算,再执行where条件限制,那么rownum就可以实现 rownum> n(n>=1)的功能.
但oralce是边执行笛卡尔集运算,边应用选择条件,所以rownum>n(n>1=)永远不成立 ( 网友评论 )
--- 自我练习,
嘿嘿 表: BOOKS
select rownum,isbn,title,price from (select * from BOOKS
order by price desc);
select *
from (select rownum ro,b.* from
BOOKS b where rownum < 10)
where ro > 5;
select * from BOOKS
where rownum < 10;
select *
from (select b.*,
rownum ro
from BOOKS b
where
rownum < 20
)
where ro > 10;
分享到:
相关推荐
本文将结合作者近日工作中,在ORACLE数据库分页查询时,遇到一个小问题,为大家讲解如何解决Oracle分页查询中排序与效率问题。
分页查询是Oracle数据库比较重要的一个知识点!希望这个对你有参照作用。
mybatis 分页 mybatis-generate Oracle数据库 大家知道mybatis自动生成代码是没有分页功能的 我在网上找了很久 有很多内容 但正真可以使用的少之又少 本人整合了网上的资源 整理了基于Oracle数据库的mybatis插件 ...
mysql,oracle,sql server分页总结与比较
java实现oracle分页策略完整版,可当做参考类使用,有详细注释,适合初学者。
关于oracle 的分页、翻页的java源代码,结合JDBC连接数据库实现,比较基础的JDBC结合swing图形框架实现,适合Java初学者使用
JSP+JDBC_真分页(基于Oracle数据库分页).
JSP+JDBC_真分页(基于Oracle数据库分页)笔记JSP+JDBC_真分页(基于Oracle数据库分页)笔记JSP+JDBC_真分页(基于Oracle数据库分页)笔记
Oracle的SQL分页实践
java+Oracle分页,java操作oracle视图,存储过程。
通用的TERADATA、ORACLE数据库分页SQL, 包含在JAVA代码中,其中的Service实现不包含在里面,需要自己实现。
数据库Oracle分页显示技术..有好东西,大家分享!!!!!!!!!!
在构建自定义搜索引擎时,开发人员常遇到的一个问题是实现某种类型的分页功能;也就是说,允许用户提交一个返回很多行数据的查询,但是只显示前20条。在用户点击一个链接时,下20条或者前20条数据会从数据库应用程序...
环境: Oracle 9i 本例子使用简单的SQL语句实现...只要看懂了分页公式,那么这是一个通用的Oracle数据库分页动作(可以使用PHP, JAVA, ASP .NET等其他语言)。 分页公式注释非常详细,是非常实用的Oracle分页语句!!
一个PHP开发Oracle数据库分页显示的PHP类,可以作为入门级Oracle开发资料收存
oracle数据库级分页 老师写的自己项目中都用这个
常用数据库分页sql(),1.oracle数据库分页;2.DB2数据库分页;3.SQL Server 2000数据库分页;4.SQL Server 2005数据库分页5.MySQL数据库分页
主要是mysql、sqlserver、oracle、db2、分页 及规律
mysql、sqlserver、oracle分页,java分页统一接口实现
JAVA,JSP,实现各类数据库分页实现大全。通用数据分页实现设计。