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);