Database Reference
In-Depth Information
-----------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-----------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 425 (100)| |
|* 1 | VIEW | | 25 | 7250 | 425 (1)| 00:00:01 |
| 2 | WINDOW SORT | | 25 | 1050 | 425 (1)| 00:00:01 |
|* 3 | TABLE ACCESS FULL| T3 | 25 | 1050 | 425 (1)| 00:00:01 |
-----------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("from$_subquery$_002"."rowlimit_$$_rownumber">5)
3 - filter("OWNER"='MARTIN')
22 rows selected.
Pagination of the result set is achieved by adding the limit clause to the query. In order to display rows 6 through
10 in the result set, the query can be changed as follows:
select /*+ gather_plan_statistics */
object_name, object_type, subobject_name from t3
where owner = 'MARTIN' order by object_name, subobject_name
offset :a rows
fetch next :a rows only;
OBJECT_NAME OBJECT_TYPE SUBOBJECT_NAME
-------------------- -------------------- --------------------
I_PARTTAB INDEX PARTITION P3
I_PARTTAB INDEX PARTITION PMAX
I_PARTTAB INDEX
PARTTAB TABLE PARTITION P1
PARTTAB TABLE PARTITION P2
Pagination is working nicely here. However, the explain plan becomes slightly more difficult to read:
SQL> select * from table(dbms_xplan.display_cursor(format=>'TYPICAL'));
PLAN_TABLE_OUTPUT
---------------------------------------------------------------------------------------
SQL_ID czwjx2jkxcj2t, child number 0
-------------------------------------
select /*+ gather_plan_statistics */ object_name, object_type,
subobject_name from t3 where owner = 'MARTIN' order by object_name,
subobject_name offset :a rows fetch next :a rows only
Plan hash value: 171896480
Search WWH ::
Custom Search