SQL> set linesize 200
SQL> set pagesize 200
SQL> alter session set statistics_level=all;
Session altered.
SQL> select * from ( select rownum as rn ,a.* from ( select
* from page a )a where rownum<=20 ) a where rn>=0 2 ;
20 rows selected.
SQL> select * from table(dbms_xplan.display_cursor(null,null,'ALLSTATS LAST'));
PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------------------------------
--------------------------------------------
SQL_ID axby03b75am6v, child number 0
-------------------------------------
select * from ( select rownum as rn ,a.* from ( select * from page a
)a where rownum<=20 ) a where rn>=0
Plan hash value: 2590430161
--------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers |
--------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | 20 |00:00:00.01 | 6 |
|* 1 | VIEW | | 1 | 20 | 20 |00:00:00.01 | 6 |
|* 2 | COUNT STOPKEY | | 1 | | 20 |00:00:00.01 | 6 |
| 3 | TABLE ACCESS FULL| PAGE | 1 | 20 | 20 |00:00:00.01 | 6 |
--------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("RN">=0)
2 - filter(ROWNUM<=20)
22 rows selected.
取第2页:
SQL> select * from ( select rownum as rn ,a.* from ( select
* from page a )a where rownum<=40 ) a where rn>=21 2 ;
20 rows selected.
SQL> select * from table(dbms_xplan.display_cursor(null,null,'ALLSTATS LAST'));
PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------------------------------
--------------------------------------------
SQL_ID crwugd6xrr3nb, child number 0
-------------------------------------
select * from ( select rownum as rn ,a.* from ( select * from page a
)a where rownum<=40 ) a where rn>=21
Plan hash value: 2590430161
--------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers |
--------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | 20 |00:00:00.01 | 6 |
|* 1 | VIEW | | 1 | 40 | 20 |00:00:00.01 | 6 |
|* 2 | COUNT STOPKEY | | 1 | | 40 |00:00:00.01 | 6 |
| 3 | TABLE ACCESS FULL| PAGE | 1 | 40 | 40 |00:00:00.01 | 6 |
--------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("RN">=21)
2 - filter(ROWNUM<=40)
22 rows selected.