Oracle ROWNUM及分页查询的一点总结
Sql代码
DROP TABLE TT3;
CREATE TABLE TT3 AS SELECT * FROM DBA_OBJECTS WHERE ROWNUM <10;
--ROWNUM < N是永远成立的
SELECT OWNER,OBJECT_NAME,ROWNUM FROM TT3 WHERE ROWNUM <10;
OWNER OBJECT_NAME ROWNUM
------------------------------ ---------------------------------------------------
SYS ICOL$ 1
SYS I_USER1 2
SYS CON$ 3
SYS UNDO$ 4
SYS C_COBJ# 5
SYS I_OBJ# 6
SYS PROXY_ROLE_DATA$ 7
SYS I_IND1 8
SYS I_CDEF2 9
--ROWNUM > N是不成立的
OWNER OBJECT_NAME ROWNUM
------------------------------ ---------------------------------------------------
--可以看出ROWNUM并不是按照排序后的结果然后分配ROWNUM,而是一开始取数据就开始分配
admin@ORCL> column OBJECT_NAME for a20;
admin@ORCL> SELECT ROWNUM,OWNER,OBJECT_NAME FROM TT3 ORDER BY OBJECT_NAME;
ROWNUM OWNER OBJECT_NAME
---------- ------------------------------ --------------------
3 SYS CON$
5 SYS C_COBJ#
1 SYS ICOL$
9 SYS I_CDEF2
8 SYS I_IND1
6 SYS I_OBJ#
2 SYS I_USER1
7 SYS PROXY_ROLE_DATA$
4 SYS UNDO$
--解决上面的问题，需要采用下面的sql
admin@ORCL> SELECT ROWNUM,OWNER,OBJECT_NAME FROM (SELECT * FROM TT3 ORDER BY OBJECT_NAME );
ROWNUM OWNER OBJECT_NAME
---------- ------------------------------ --------------------
1 SYS CON$
2 SYS C_COBJ#
3 SYS ICOL$
4 SYS I_CDEF2
5 SYS I_IND1
6 SYS I_OBJ#
7 SYS I_USER1
8 SYS PROXY_ROLE_DATA$
9 SYS UNDO$
--测试若按照非唯一值去分页，然后查看是否获得非重复值，结果存在重复值
DROP TABLE TT5;
CREATE TABLE TT5 AS SELECT * FROM DBA_OBJECTS WHERE ROWNUM <10;
INSERT INTO TT5 SELECT * FROM TT5;
INSERT INTO TT5 SELECT * FROM TT5;
INSERT INTO TT5 SELECT * FROM TT5;
COMMIT;
--共产生了72条数据
admin@ORCL> SELECT COUNT(*) FROM TT5;
COUNT(*)
----------
72
--查询是否有重复的ROWID，若有则代表重复
SELECT RD FROM
(
SELECT RD FROM
(SELECT ROWNUM RN,ROWID RD, TT.* FROM (SELECT * FROM TT5 ORDER BY OBJECT_NAME)TT WHERE ROWNUM <= 10) A
WHERE A.RN >= 1
UNION ALL
SELECT RD FROM
(SELECT ROWNUM RN,ROWID RD, TT.* FROM (SELECT * FROM TT5 ORDER BY OBJECT_NAME)TT WHERE ROWNUM <= 20) A
WHERE A.RN >= 11
UNION ALL
SELECT RD FROM
(SELECT ROWNUM RN,ROWID RD, TT.* FROM (SELECT * FROM TT5 ORDER BY OBJECT_NAME)TT WHERE ROWNUM <= 30) A
WHERE A.RN >= 21
UNION ALL
SELECT RD FROM
(SELECT ROWNUM RN,ROWID RD, TT.* FROM (SELECT * FROM TT5 ORDER BY OBJECT_NAME)TT WHERE ROWNUM <= 40) A
WHERE A.RN >= 31
UNION ALL
SELECT RD FROM
(SELECT ROWNUM RN,ROWID RD, TT.* FROM (SELECT * FROM TT5 ORDER BY OBJECT_NAME)TT WHER