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

SQL> select *
2 from ( select /*+ FIRST_ROWS(n) */
3 a.*, ROWNUM rnum
4 from ( select object_name, object_type, subobject_name from t3
5 where owner = 'MARTIN'
6 order by object_name, subobject_name ) a
7 where ROWNUM <= :last_row_in_set )
8 where rnum >= :first_row_in_set
9 /
The bind variables here constitute the clever bit of the query. In the initial query execution first_row_in_set is set
to 1 while last_row_in_set is set to 5. The bind variables are then changed with every execution of the query allowing
for a rolling window.
The complete result set for the user MARTIN is important for the rest of this section. Querying table T3 for all
objects owned by MARTIN, and ordering the results by object_name and subobject_name, the following result is
displayed:
SQL select object_name,object_type,subobject_name
2 from t3 where owner = 'MARTIN'
3 order by object_name,subobject_name;
OBJECT_NAME OBJECT_TYPE SUBOBJECT_NAME
-------------------- -------------------- --------------------
ANSWER FUNCTION
DEBUG_PROC PROCEDURE
DEBUG_TAB TABLE
I_PARTTAB INDEX PARTITION P1
I_PARTTAB INDEX PARTITION P2
I_PARTTAB INDEX PARTITION P3
I_PARTTAB INDEX PARTITION PMAX
I_PARTTAB INDEX
PARTTAB TABLE PARTITION P1
PARTTAB TABLE PARTITION P2
PARTTAB TABLE PARTITION P3
PARTTAB TABLE PARTITION P4
PARTTAB TABLE PARTITION P5
PARTTAB TABLE PARTITION PMAX
PARTTAB TABLE
PKG_3 PACKAGE
PKG_5 PACKAGE
PKG_5 PACKAGE BODY
PK_T1 INDEX
S SEQUENCE
SEQTEST TABLE
SYS_C009851 INDEX
S_T1 SEQUENCE
T1 TABLE
T3 TABLE
25 rows selected.