oracle中rowid高速分页

第一步：获取数据物理地址
第二步：取得最大页数
第三步：取得最小页数
第四步：因为取得的页数都是物理地址，再根据物理地址，查询出具体数据

--rowid分页，第一步

select rowid rid,OWNER,OBJECT_NAME,LAST_DDL_TIME from tt order by LAST_DDL_TIME desc

RID                OWNER                          OBJECT_NAME          LAST_DDL_TIME
------------------ ------------------------------ -------------------- -------------------
AAAVciAABAAAXEZAAC SYS                            CON$                 2013-08-24 11:52:40
AAAVciAABAAAXEZAAA SYS                            ICOL$                2013-08-24 11:47:37
AAAVciAABAAAXEZAAE SYS                            C_COBJ#              2013-08-24 11:37:35
AAAVciAABAAAXEZAAF SYS                            I_OBJ#               2013-08-24 11:37:35
AAAVciAABAAAXEZAAG SYS                            PROXY_ROLE_DATA$     2013-08-24 11:37:35
AAAVciAABAAAXEZAAH SYS                            I_IND1               2013-08-24 11:37:35
AAAVciAABAAAXEZAAI SYS                            I_CDEF2              2013-08-24 11:37:35
AAAVciAABAAAXEZAAJ SYS                            I_OBJ5               2013-08-24 11:37:35
AAAVciAABAAAXEZAAK SYS                            I_PROXY_ROLE_DATA$_1 2013-08-24 11:37:35
AAAVciAABAAAXEZAAL SYS                            FILE$                2013-08-24 11:37:35
AAAVciAABAAAXEZAAM SYS                            UET$                 2013-08-24 11:37:35
AAAVciAABAAAXEZAAN SYS                            I_FILE#_BLOCK#       2013-08-24 11:37:35
AAAVciAABAAAXEZAAO SYS                            I_FILE1              2013-08-24 11:37:35
AAAVciAABAAAXEZAAP SYS                            I_CON1               2013-08-24 11:37:35
AAAVciAABAAAXEZAAQ SYS                            I_OBJ3               2013-08-24 11:37:35
AAAVciAABAAAXEZAAR SYS                            I_TS#                2013-08-24 11:37:35
AAAVciAABAAAXEZAAD SYS                            UNDO$                2013-08-24 11:37:35
AAAVciAABAAAXEZAAS SYS                            I_CDEF4              2013-08-24 11:37:35
AAAVciAABAAAXEZAAB SYS                            I_USER1              2013-08-24 11:37:35

19 rows selected.



--rowid分页，第二步

select rownum rn,rid from(select rowid rid,OWNER,OBJECT_NAME,LAST_DDL_TIME from tt order by LAST_DDL_TIME desc) where rownum<10;

        RN RID
---------- ------------------
         1 AAAVciAABAAAXEZAAC
         2 AAAVciAABAAAXEZAAA
         3 AAAVciAABAAAXEZAAB
         4 AAAVciAABAAAXEZAAD
         5 AAAVciAABAAAXEZAAE
         6 AAAVciAABAAAXEZAAF
         7 AAAVciAABAAAXEZAAG
         8 AAAVciAABAAAXEZAAH
         9 AAAVciAABAAAXEZAAI

9 rows selected.


--rowid分页，第三步
select rid from(select rownum rn,rid from(select rowid rid,OWNER,OBJECT_NAME,LAST_DDL_TIME from tt order by LAST_DDL_TIME desc) where rownum<10) where rn>5;

RID
------------------
AAAVciAABAAAXEZAAF
AAAVciAABAAAXEZAAG
AAAVciAABAAAXEZAAH
AAAVciAABAAAXEZAAI


--rowid分页，第四步
select * from tt where rowid in(select rid from(select rownum rn,rid from(select rowid rid,OWNER,OBJECT_NAME,LAST_DDL_TIME from tt order by LAST_DDL_TIME desc) where rownum<10) where rn>5);

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/31397003/viewspace-2143695/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/31397003/viewspace-2143695/