Oracle 12c New Features - Expert Consolidation in Oracle Database 12c - page 37

Now, getting back to the pagination query, it indeed returns the first 5 rows:
OBJECT_NAME OBJECT_TYPE SUBOBJECT_NAME RNUM
-------------------- -------------------- -------------------- -----------
ANSWER FUNCTION 1
DEBUG_PROC PROCEDURE 2
DEBUG_TAB TABLE 3
I_PARTTAB INDEX PARTITION P1 4
I_PARTTAB INDEX PARTITION P2 5
The next 5 records where then fetched by changing the where clause in lines 6 and 7 of the query. This whole
query can now be greatly simplified thanks to the new “row limiting clause” in the select statement. Two options exist:
using an offset and either a number of rows or a percentage of the result set can be returned. Returning to the above
example, the offset is useful when a certain number of rows in the result set have to be skipped. Skipping the first five
rows in the result set is achieved using this query:
SQL> select object_name, object_type, subobject_name from t3
2 where owner = 'MARTIN' order by object_name, subobject_name
3 offset 5 rows;
OBJECT_NAME OBJECT_TYPE SUBOBJECT_NAME
-------------------- -------------------- --------------------
I_PARTTAB INDEX PARTITION P3
I_PARTTAB INDEX PARTITION PMAX
I_PARTTAB INDEX
PARTTAB TABLE PARTITION P1
[...]
T1 TABLE
T3 TABLE
20 rows selected.
As you would expect, the first 5 objects are not part of the result set. The query however does not limit the result
set. Keep the execution plan of this query in mind, which is as follows:
SQL> select * from table(dbms_xplan.display_cursor);
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
SQL_ID fyrpmgskxx073, child number 0
-------------------------------------
select object_name, object_type, subobject_name from t3 where owner =
'MARTIN' order by object_name, subobject_name offset 5 rows
Plan hash value: 3729804300