文章探讨了在Oracle数据库中进行分页查询的性能问题，提供了四种随机选取记录的解决方案，包括使用DBMS_RANDOM函数、ROW_NUMBER函数和SAMPLE函数。同时，文章分析了ROWNUM和rowid在分页查询中的区别，以及如何通过调整SQL语句结构来优化性能，如使用物理分页行编号rowid。在千万级数据表的分页查询中，通过ROWID进行分页能显著提高查询效率。
select * from (select rownum no, a.* from a
where status_code ='AVAILABLE' and id_type = 'MEM' and archive_flag = 'N' and rownum<=1000000 ) where no >=1000000-1 for update
随机取一条，锁住记录，操作完archive_flag = 'Y'不会再取。
-
四种解决方案： -
测试数据90万条 -
方案一：使用dbms_random.random 对数据排序，然后取出第一条,不过这种效率太低，用时3-4秒。 -
SELECT *FROM (SELECT * FROM TEMP_CODES ORDER BY dbms_random.random) where rownum =1 -
方案二：使用dbms_random.value 对数据排序，与方案一如出一辙 -
select * from (select * from TEMP_CODES order by dbms_random.value) where rownum<=1 -
或者使用row_number 函数 -
SELECT CODE -
FROM ( SELECT CODE, ROW_NUMBER() OVER (ORDER BY dbms_random.value) rn FROM TEMP_CODES ) -
WHERE rn = 1; -
方案三：使用sample随机取样，取得一条随机行，由于是随机样，所以在数据量逐渐变少的时候，取样会出现无法取到的情况，可以通过多次取样来满足始终能取到数据。 -
选择0.001%的记录 -
select *from TEMP_CODES sample(0.001) WHERE FLAG=0 AND ROWNUM=1; -
选择20%的记录 -
select *from TEMP_CODES sample(20) WHERE FLAG=0 AND ROWNUM=1; -
选择50%的记录 -
select *from TEMP_CODES sample(50) WHERE FLAG=0 AND ROWNUM=1; -
选择70%的记录 -
select *from TEMP_CODES sample(70) WHERE FLAG=0 AND ROWNUM=1; -
选择99.99%的记录 -
select *from TEMP_CODES sample(99.9999) WHERE FLAG=0 AND ROWNUM=1; -
方案四：查询包含条件的记录总数，通过这个记录总数随机生成一个小于等于这个记录总数的数，通过这个随机数去取得记录行。 -
1、查询剩余的总数记录数：当前为90万 -
2、生成1-90万之间的随机数randomNum -
3、通过随机数查询。 -
select * from (select rownum no,code from TEMP_CODES where rownum<=randomNum ) where no >=randomNum -
以上四种方案皆可得到随机一行记录。个人还是比较倾向于方案三。
-
1.根据ROWID来分
-
select * from t_xiaoxi where rowid in(select rid from (select rownum rn,rid from(select rowid rid,cid from
-
t_xiaoxi order by cid desc) where rownum<10000) where rn>9980) order by cid desc;
-
2.按分析函数来分
-
select * from (select t.*,row_number() over(order by cid desc) rk from t_xiaoxi t) where rk<10000 and rk>9980;
-
3.按ROWNUM来分
-
select * from(select t.*,rownum rn from(select * from t_xiaoxi order by cid desc) t where rownum<10000) where
-
rn>9980
记录一次Oracle数据库千万级数据表的分页性能优化
项目背景：
公司有一个项目用的是oracle数据库，用户数据量比较大。有1200w+(其实也不多)。然后在分页查询的时候到300w左右的时候就比较慢了，大概要3s+，到600w左右的时候要6s+。其实这个表的查询并不复杂。就是一个单表的数据分页查询。
原来的分页SQL-1:
通过使用逻辑分页行编号值ROWNUM进行分页
SELECT * FROM
(SELECT
T.*, ROWNUM AS ROWNO FROM
(SELECT
CUST_ID,
CUST_NAME,
CUST_NAT,
CERT_NO,
ORG_ID,
TO_CHAR(CREATE_DT, 'YYYY-MM-DD') AS CREATE_DT,
GENDER
FROM xx_xx
WHERE 1 = 1) T) R
WHERE ROWNO >= 3000000 AND ROWNO <= 3000050;
修改过的分页SQL-2：
也是通过逻辑分页编号值ROWNUM进行分页
SELECT * FROM
(SELECT
T.*,
ROWNUM AS ROWNO
FROM
(SELECT
CUST_ID,
CUST_NAME,
CUST_NAT,
CERT_NO,
ORG_ID,
TO_CHAR(CREATE_DT, 'YYYY-MM-DD') AS CREATE_DT,
GENDER
FROM xx_xxWHERE
1 = 1 AND ROWNUM <= 3000050) T) R
WHERE ROWNO>3000000;
修改过的分页SQL-3：
使用物理分页行编号值rowid
SELECT
CUST_ID,
CUST_NAME,
CUST_NAT,
CERT_NO,
ORG_ID,
TO_CHAR(CREATE_DT, 'YYYY-MM-DD') AS CREATE_DT,
GENDER
FROM
T2A_CUST_I
WHERE rowid IN (
SELECT t2.rid
FROM
(SELECT
t1.*,
ROWNUM rn
FROM
(SELECT
CUST_ID,
CUST_NAME,
CUST_NAT,
CERT_NO,
ORG_ID,
TO_CHAR(CREATE_DT, 'YYYY-MM-DD') AS CREATE_DT,
GENDER,
rowid AS rid
FROM xx_xx
WHERE 1 = 1) t1)t2
WHERE rn>3000000 AND rn < 3000050);
分析：
1、ROWNUM和rowid的区别？ ROWNUM是逻辑地址，表示查询耨条记录在整个结果集中的位置，同一条记录查询条件不同对应的rownum是不同的二rowid是不会变的。rowid是物理地址，用于定位数据表中某条数据的位置，是唯一的、不会改变的，查询快
2、SQL-1和SQL-2都是通过ROWNUM来分页的，效果为什么会有很大差距？这个网上很多都是说CBO优化模式，Oracle可以讲外层的查询条件推到内层查询中，以提高内层查询的执行效率。很多帖子都在。大家自己可以了解一下。
| 3000000-3000050 | 6000000-6000050 | 9000000-9000050 | |
| SQL-1 | 7.8s | 8s | 8.2 |
| SQL-2 | 2.2s | 4.4s | 6.5s |
| SQL-3 | 2.9s | 2.8s | 2.7 |
对于Oracle中分页排序查询语句执行效率的比较分析
作者：lzgame
在工作中我们经常遇到需要在Oracle中进行分页、排序、查询的组合SQL语句，举例来说，通常我们会这样写：（假定表test中id是主键，并且id从1开始没有间断顺序排列）
1． SELECT * FROM (
SELECT id,a1,a2,a3,a4,a5,a6,a7,a8,a9, ROWNUM AS rn FROM test
WHERE a1 LIKE '%%' AND ROWNUM<=1000000
ORDER BY id
) t2 WHERE rn>=999990;
但是由于ROWNUM是一个伪列，Oracle会首先查询满足ROWNUM<=1000000条件的记录，然后再对得到的记录进行排序，这就导致我们并不能获得期望的排序结果。本应得到id为999990-1000000，实际上却得到了一些无规律并令人困惑的id结果。于是我做了以下改进；
2． SELECT * FROM (
SELECT id,a1,a2,a3,a4,a5,a6,a7,a8,a9, ROWNUM AS rn FROM (
SELECT id,a1,a2,a3,a4,a5,a6,a7,a8,a9 FROM test
WHERE a1 LIKE '%%'
ORDER BY id
) t1 WHERE ROWNUM<=1000000
) t2 WHERE rn>=999990;
显然，通过经典的三层分页排序查询结构，我确实得到了想要的结果。但是在数据量很大的数据表中，由于需要首先取得排序后的全部数据集，导致了执行效率的极速降低，直至无法忍受。为了提高性能，我在网上查阅了很多资料，其中有一种说法是：当排序条件使用的关键字是主键或索引，并在WHERE子句中先于ROWNUM使用过该关键字时，我们就可以采用1号语句获得想要的结果了，于是我改写了1号语句并进行了测试：
3． SELECT * FROM (
SELECT id,a1,a2,a3,a4,a5,a6,a7,a8,a9, ROWNUM AS rn FROM test
WHERE id>=0 AND ROWNUM<=1000000
ORDER BY id
) t2 WHERE rn>=999990;
我惊喜的发现确实有效，我成功了，但惊喜并没有持续多久，因为当我把查询条件“a1 LIKE '%%'”也放回WHERE语句中时，一切又恢复了原状。于是新一轮的资料查找又开始了。但网上的资料似乎始终没有脱离以上3种语句的范围，在漫长的查找后我快要放弃了，忽然在一条论坛回复中我看见了新的曙光：用WHERE…IN语句。我立刻改写出了4号语句：
4．SELECT * FROM test
WHERE id IN (
SELECT id FROM (
SELECT id, ROWNUM AS rn FROM (
SELECT id FROM test
WHERE a1 LIKE '%%'
ORDER BY id
) t1 WHERE ROWNUM<=1000000
) t2 WHERE rn>=999990
);
这条语句通过减少最内层SELECT语句获得的数据量（仅保留必须的id），极大地提高了查询性能。但是WHERE…IN语句由于需要遍历数据表，也就是说在本语句中每查询出一个id，Oracle就需要在最后的WHERE…IN语句搜索一次并把它挑出来，所以WHERE…IN语句本身的效率并不高，本语句依然存在着效率提升的空间，那么该怎么做呢？答案就是ROWID伪列。什么是ROWID伪列呢？用最简单的话说，ROWID就是该数据行的绝对物理地址，在百度百科上我们可以查到索引就是通过ROWID来记录数据位置的。于是我们的WHERE…IN语句不再需要遍历数据表，不再需要通过一次次的检索来收集数据了，因为它得到了最终数据的直接物理地址。从这个意义上讲，WHERE…IN语句成为了最高效的语句。
语句改写如下：
5．SELECT * FROM test
WHERE ROWID IN (
SELECT rid FROM (
SELECT rid, ROWNUM AS rn FROM (
SELECT ROWID rid FROM test
WHERE a1 LIKE '%%'
ORDER BY id
) t1 WHERE ROWNUM<=1000000
) t2 WHERE rn>=999990
);
令人困惑的是，虽然5号语句检索出来的数据范围是正确的，但是最终的顺序是被打乱的，也就是说id虽然范围是在999990-1000000中，但相互间是没有顺序的，当然到这一步已经没有什么难度了，于是我写出了最终的语句：
6. SELECT * FROM test
WHERE ROWID IN (
SELECT rid FROM (
SELECT rid, ROWNUM AS rn FROM (
SELECT ROWID rid FROM test
WHERE a1 LIKE '%%'
ORDER BY id
) t1 WHERE ROWNUM<=1000000
) t2 WHERE rn>=999990
) ORDER BY id;
至此一切OK，为了对提升的效率有个直观的理解，我又对1、2、4、6号语句进行了测试（测试数据表test共1011003条记录），各测试三次去平均值，结果如下：
1号语句（两层嵌套，而且结果并不符合预期）：21.98秒
2号语句（三层嵌套，结果符合预期，但实在是太慢了。。。）：48.62秒
4号语句（采用WHERE id IN …，结果符合预期，效率极大提高）：11.49秒
6号语句（采用WHERE ROWID IN …，结果符合预期，效率最高）：5.98秒
以上是我对Oracle中分页排序查询语句执行效率的一点理解，如果有什么错误的地方请大家指正，谢谢！
扫一扫
1801
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
