Paging before join and aggregation

First test data.

drop table chi purge;

drop table par purge;


create table par 
( o varchar2(30)
, t varchar2(30)
, n varchar2(30)
, constraint par_pk primary key(o,t,n)) 
organization index ;

create table chi 
( o varchar2(30)
, t varchar2(30)
, n varchar2(30)
, c varchar2(30)
, constraint chi_pk primary key(o,t,n,c)
, constraint chi_par_fk foreign key(o,t,n) references par(o,t,n)) 
organization index ;

insert into par select distinct owner, object_type, object_name from dba_objects;

insert into chi(o,t,n,c) 
select distinct ta.owner, ob.object_type, ta.table_name, ta.column_name 
from all_tab_columns ta 
inner join all_objects ob 
   on ta.owner=ob.owner and TA.TABLE_NAME = ob. object_name
;

commit;

exec dbms_stats.gather_table_stats(user,'PAR');

exec dbms_stats.gather_table_stats(user,'CHI');

Query to start with and the info about execution.

SQL> SELECT n,t,o,cou
  2    FROM (  SELECT pa.o, pa.t, pa.n, COUNT ( * ) cou
  3              FROM par pa LEFT OUTER JOIN chi ch
  4                      ON pa.o = ch.o and pa.t = ch.t and pa.n = ch.n
  5          GROUP BY pa.o, pa.t, pa.n
  6          ORDER BY pa.t, pa.o, pa.n)
  7   WHERE ROWNUM < 3;

N                              T                              O                                     COU
------------------------------ ------------------------------ ------------------------------ ----------
C_COBJ#                        CLUSTER                        SYS                                     1
C_FILE#_BLOCK#                 CLUSTER                        SYS                                     3

Elapsed: 00:00:02.26

----------------------------------------------------------------------------------------------------
| Id  | Operation                       | Name   | Starts | E-Rows | A-Rows |   A-Time   | Buffers |
----------------------------------------------------------------------------------------------------
|*  1 |  COUNT STOPKEY                  |        |      1 |        |      2 |00:00:02.19 |    2014 |
|   2 |   VIEW                          |        |      1 |    112K|      2 |00:00:02.19 |    2014 |
|*  3 |    SORT GROUP BY STOPKEY        |        |      1 |    112K|      2 |00:00:02.19 |    2014 |
|   4 |     MERGE JOIN OUTER            |        |      1 |    112K|    166K|00:00:01.93 |    2014 |
|   5 |      SORT JOIN                  |        |      1 |  64558 |  64558 |00:00:00.52 |     666 |
|   6 |       INDEX FULL SCAN DESCENDING| PAR_PK |      1 |  64558 |  64558 |00:00:00.06 |     666 |
|*  7 |      SORT JOIN                  |        |  64558 |    112K|    112K|00:00:00.87 |    1348 |
|   8 |       INDEX FULL SCAN           | CHI_PK |      1 |    112K|    112K|00:00:00.11 |    1348 |
----------------------------------------------------------------------------------------------------

The alternative to do paging first and aggregate only the rows to be retrieved.

Paging done in par inline view line 9, order by need to be done on both line 8 for paging and 14 to get the results back in order after joining and aggregating.

SQL>   SELECT pap.n,
  2           pap.t,
  3           pap.o,
  4           COUNT ( * ) cou
  5      FROM (SELECT *
  6              FROM (  SELECT pa.o, pa.t, pa.n
  7                        FROM par pa
  8                    ORDER BY pa.t, pa.o, pa.n)
  9             WHERE ROWNUM < 3
 10            ) pap
 11       LEFT OUTER JOIN chi ch
 12         ON pap.o = ch.o AND pap.t = ch.t AND pap.n = ch.n
 13  GROUP BY pap.o, pap.t, pap.n
 14  ORDER BY pap.t, pap.o, pap.n;

N                              T                              O                                     COU
------------------------------ ------------------------------ ------------------------------ ----------
C_COBJ#                        CLUSTER                        SYS                                     1
C_FILE#_BLOCK#                 CLUSTER                        SYS                                     3

Elapsed: 00:00:00.20

-----------------------------------------------------------------------------------------------
| Id  | Operation                  | Name   | Starts | E-Rows | A-Rows |   A-Time   | Buffers |
-----------------------------------------------------------------------------------------------
|   1 |  SORT GROUP BY NOSORT      |        |      1 |      2 |      2 |00:00:00.20 |     673 |
|   2 |   NESTED LOOPS OUTER       |        |      1 |      2 |      4 |00:00:00.20 |     673 |
|   3 |    VIEW                    |        |      1 |      2 |      2 |00:00:00.20 |     666 |
|*  4 |     COUNT STOPKEY          |        |      1 |        |      2 |00:00:00.20 |     666 |
|   5 |      VIEW                  |        |      1 |  64558 |      2 |00:00:00.20 |     666 |
|*  6 |       SORT ORDER BY STOPKEY|        |      1 |  64558 |      2 |00:00:00.20 |     666 |
|   7 |        INDEX FULL SCAN     | PAR_PK |      1 |  64558 |  64558 |00:00:00.06 |     666 |
|*  8 |    INDEX RANGE SCAN        | CHI_PK |      2 |      1 |      4 |00:00:00.01 |       7 |
-----------------------------------------------------------------------------------------------

Notice cou column cannot be in order by condition.
Join needs to be outer.