Summary of Oracle ROWNUM and paging Query

Summary of Oracle ROWNUM and paging query: SQL code DROP TABLE TT3; CREATE TABLE TT3 AS SELECT * FROM DBA_OBJECTS WHERE ROWNUM <10; -- ROWNUM <N is a permanent SELECT OWNER, OBJECT_NAME, rownum from TT3 where rownum <10; OWNER OBJECT_NAME ROWNUM extends 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 is an invalid OWNER OBJECT_NAME ROWNUM ---------------------------------------------------------------------------. It can be seen that ROWNUM is not allocated according to the sorted result, instead, the admin @ ORCL> column OBJECT_NAME for a20; admin @ ORCL> select rownum, OWNER, OBJECT_NAME FROM TT3 order by OBJECT_NAME; rownum owner OBJECT_NAME ---------- specified partition 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 $ -- solve the problem above, use the following SQL admin @ ORCL> SELECT ROWNUM, OWNER, OBJECT_NAME FROM (SELECT * FROM TT3 ORDER BY OBJECT_NAME ); rownum owner OBJECT_NAME ---------- specified partition 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 $ -- if the page is displayed based on a non-unique value, then, check whether non-repeated values are obtained. duplicate values exist in the results: 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 data records are generated: admin @ ORCL> select count (*) FROM TT5; COUNT (*) ---------- 72 -- check whether duplicate rowids exist. If yes, duplicate select rd from (select rownum rn, rowid rd, TT. * FROM (SELECT * FROM TT5 order by OBJECT_NAME) tt where rownum <= 10) a where. 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. 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. 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. RN> = 31 union all select rd from (select rownum rn, rowid rd, TT. * FROM (SELECT * FROM TT5 order by OBJECT_NAME) tt where rownum <= 50) a where. RN> = 41 union all select rd from (select rownum rn, rowid rd, TT. * FROM (SELECT * FROM TT5 order by OBJECT_NAME) tt where rownum <= 60) a where. RN> = 51 union all select rd from (select rownum rn, rowid rd, TT. * FROM (SELECT * FROM TT5 order by OBJECT_NAME) tt where rownum <= 71) a where. RN> = 61) a group by rd having count (*)> 1; RD ------------------ AAAPB2AAEAAAFS1AAt AAAPB2AAEAAAFS1AAu AAAPB2AAEAAAFS1AAf duplicate values -- the following method does not produce duplicate values, because the data value sequence does not change select rd from (select rownum rn, rowid rd, TT. * FROM (SELECT * FROM TT5) tt where rownum <= 10) a where. RN> = 1 union all select rd from (select rownum rn, rowid rd, TT. * FROM (SELECT * FROM TT5) tt where rownum <= 20) a where. RN> = 11 union all select rd from (select rownum rn, rowid rd, TT. * FROM (SELECT * FROM TT5) tt where rownum <= 30) a where. RN> = 21 union all select rd from (select rownum rn, rowid rd, TT. * FROM (SELECT * FROM TT5) tt where rownum <= 40) a where. RN> = 31 union all select rd from (select rownum rn, rowid rd, TT. * FROM (SELECT * FROM TT5) tt where rownum <= 50) a where. RN> = 41 union all select rd from (select rownum rn, rowid rd, TT. * FROM (SELECT * FROM TT5) tt where rownum <= 60) a where. RN> = 51 union all select rd from (select rownum rn, rowid rd, TT. * FROM (SELECT * FROM TT5) tt where rownum <= 71) a where. RN> = 61) a group by rd having count (*)> 1;