SQL> declare
   2   
   3     type TIntTable is table of integer ;
   4   
   5     cursor crTest is
   6       select rownum n#
   7       from dba_objects, dba_objects, dba_objects, dba_objects,
   8            dba_objects, dba_objects, dba_objects, dba_objects ;
   9   
  10     time_start  timestamp := systimestamp ;
  11     obj_count   integer ;
  12     fetch_count integer :=  1  ;
  13     data        TIntTable ;
  14   
  15     procedure Mark ( msg varchar2 ) is
  16       seconds number := to_char ( systimestamp, 'SSSSS.FF' ) - to_char ( time_start, 'SSSSS.FF' ) ;
  17     begin
  18       dbms_output.put_line ( rpad ( msg || ' ',  70 , '.' ) || to_char ( seconds, '9990.00' )) ;
  19       time_start := systimestamp ;
  20     end ;
  21   
  22     procedure FetchNext is
  23       N constant integer :=  1000000  ;
  24     begin
  25       fetch crTest bulk collect into data limit N ;
  26       Mark ( 'Получение записей ' || fetch_count || ' - ' || ( fetch_count + N -  1  )) ;
  27       fetch_count := fetch_count + N ;
  28     end ;
  29   
  30   begin
  31     select count(*) into obj_count from dba_objects ;
  32     Mark ( 'Запрос кол-ва записей в dba_objects' ) ;
  33     open crTest ;
  34     Mark ( 'Открытие курсора на ' || power ( obj_count,  8  ) || ' записей' ) ;
  35     for i in  1 .. 10  loop FetchNext ; end loop ;
  36   end ;
  37   /

Запрос кол-ва записей в dba_objects ..................................     0 . 05 
Открытие курсора на  32326965938056011787168549511729891841  записей ...     0 . 00 
Получение записей  1  -  1000000  ........................................     1 . 19 
Получение записей  1000001  -  2000000  ..................................     0 . 75 
Получение записей  2000001  -  3000000  ..................................     0 . 77 
Получение записей  3000001  -  4000000  ..................................     0 . 75 
Получение записей  4000001  -  5000000  ..................................     0 . 73 
Получение записей  5000001  -  6000000  ..................................     0 . 77 
Получение записей  6000001  -  7000000  ..................................     0 . 75 
Получение записей  7000001  -  8000000  ..................................     0 . 75 
Получение записей  8000001  -  9000000  ..................................     0 . 73 
Получение записей  9000001  -  10000000  .................................     0 . 75 

PL/SQL procedure successfully completed

SQL> create table t1 as select rownum id from dual connect by level <=  1000000  ;
SQL> create table t2 as select rownum id from dual connect by level <=  1000000  ;
SQL> create table t3 as select rownum id from dual connect by level <=  100  ;
SQL> alter table t1 add constraint t1_pk primary key ( id ) ;
SQL> alter table t2 add constraint t2_pk primary key ( id ) ;
SQL> alter table t3 add constraint t3_pk primary key ( id ) ;
SQL> exec dbms_stats.gather_schema_stats ( ownname => user ) ;

-- Нормальный план для запроса с подзапросом. Оптимизатор раскрывает подзапрос и 
-- меняет порядок join-ов, вытаскивая вперед маленькую таблицу t3

SQL> explain plan for
   2   select *
   3   from
   4     ( select t1.id id_1, t2.id id_2 from t1, t2 where t1.id = t2.id ) tt,
   5     t3
   6   where
   7     tt.id_1 = t3.id ;

-----------------------------------------------------------------------------
| Id  | Operation           | Name  | Rows  | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------
|    0  | SELECT STATEMENT    |       |      1  |     11  |      3    ( 0 )|  00 : 00 : 01  |
|    1  |  NESTED LOOPS       |       |      1  |     11  |      3    ( 0 )|  00 : 00 : 01  |
|    2  |   NESTED LOOPS      |       |      1  |      7  |      2    ( 0 )|  00 : 00 : 01  |
|    3  |    INDEX FULL SCAN  | T3_PK |    100  |    300  |      1    ( 0 )|  00 : 00 : 01  |
|*   4  |    INDEX UNIQUE SCAN| T1_PK |      1  |      4  |      1    ( 0 )|  00 : 00 : 01  |
|*   5  |   INDEX UNIQUE SCAN | T2_PK |      1  |      4  |      1    ( 0 )|  00 : 00 : 01  |
-----------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
    4  - access("T1"."ID"="T3"."ID")
    5  - access("T1"."ID"="T2"."ID")

-- Хинт NO_MERGE запрещает раскрывать подзапрос

SQL> explain plan for
   2   select *
   3   from
   4     ( select /*+ NO_MERGE */ t1.id id_1, t2.id id_2 from t1, t2 where t1.id = t2.id ) tt,
   5     t3
   6   where
   7     tt.id_1 = t3.id ;

--------------------------------------------------------------------------------
| Id  | Operation            | Name  | Rows  | Bytes |TempSpc| Cost (%CPU)| Time
--------------------------------------------------------------------------------
|    0  | SELECT STATEMENT     |       |      1  |     29  |       |   2188    ( 4 )|  00 : 0 
|*   1  |  HASH JOIN           |       |      1  |     29  |       |   2188    ( 4 )|  00 : 0 
|    2  |   INDEX FULL SCAN    | T3_PK |    100  |    300  |       |      1    ( 0 )|  00 : 0 
|    3  |   VIEW               |       |  1000K|    24M|       |   2177    ( 3 )|  00 : 0 
|*   4  |    HASH JOIN         |       |  1000K|  7812K|    15M|   2177    ( 3 )|  00 : 0 
|    5  |     TABLE ACCESS FULL| T1    |  1000K|  3906K|       |    315    ( 5 )|  00 : 0 
|    6  |     TABLE ACCESS FULL| T2    |  1000K|  3906K|       |    315    ( 5 )|  00 : 0 
--------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
    1  - access("TT"."ID_1"="T3"."ID")
    4  - access("T1"."ID"="T2"."ID")

-- Продолжая издевательства, запрещаем использовать HASH JOIN
-- для подключения t3 к результату подзапроса

SQL> explain plan for
   2   select /*+ NO_USE_HASH (tt, t3) */ *
   3   from
   4     ( select /*+ NO_MERGE */ t1.id id_1, t2.id id_2 from t1, t2 where t1.id = t2.id ) tt,
   5     t3
   6   where
   7     tt.id_1 = t3.id ;

--------------------------------------------------------------------------------
| Id  | Operation            | Name  | Rows  | Bytes |TempSpc| Cost (%CPU)| Time
--------------------------------------------------------------------------------
|    0  | SELECT STATEMENT     |       |      1  |     29  |       |   2851   ( 26 )|  00 : 0 
|    1  |  NESTED LOOPS        |       |      1  |     29  |       |   2851   ( 26 )|  00 : 0 
|    2  |   VIEW               |       |  1000K|    24M|       |   2177    ( 3 )|  00 : 0 
|*   3  |    HASH JOIN         |       |  1000K|  7812K|    15M|   2177    ( 3 )|  00 : 0 
|    4  |     TABLE ACCESS FULL| T1    |  1000K|  3906K|       |    315    ( 5 )|  00 : 0 
|    5  |     TABLE ACCESS FULL| T2    |  1000K|  3906K|       |    315    ( 5 )|  00 : 0 
|*   6  |   INDEX UNIQUE SCAN  | T3_PK |      1  |      3  |       |      0    ( 0 )|  00 : 0 
--------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
    3  - access("T1"."ID"="T2"."ID")
    6  - access("TT"."ID_1"="T3"."ID")

-- Наконец, запретим использовать индекс T3_PK. Обратите внимание, мы
-- запретили HASH JOIN, поэтому в прошлом случае оптимизатор выбрал NESTED LOOPS, 
-- а сейчас - MERGE JOIN. 

SQL> explain plan for
   2   select /*+ NO_USE_HASH (tt, t3) NO_INDEX (t3 t3_pk) */ *
   3   from
   4     ( select /*+ NO_MERGE */ t1.id id_1, t2.id id_2 from t1, t2 ) tt,
   5     t3
   6   where
   7     tt.id_1 = tt.id_2 and tt.id_1 = t3.id ;

--------------------------------------------------------------------------------
| Id  | Operation             | Name | Rows  | Bytes |TempSpc| Cost (%CPU)| Time
--------------------------------------------------------------------------------
|    0  | SELECT STATEMENT      |      |      1  |     29  |       |   9638    ( 2 )|  00 : 0 
|    1  |  MERGE JOIN           |      |      1  |     29  |       |   9638    ( 2 )|  00 : 0 
|    2  |   SORT JOIN           |      |    100  |    300  |       |      3   ( 34 )|  00 : 0 
|    3  |    TABLE ACCESS FULL  | T3   |    100  |    300  |       |      2    ( 0 )|  00 : 0 
|*   4  |   SORT JOIN           |      |  1000K|    24M|    69M|   9635    ( 2 )|  00 : 0 
|    5  |    VIEW               |      |  1000K|    24M|       |   2177    ( 3 )|  00 : 0 
|*   6  |     HASH JOIN         |      |  1000K|  7812K|    15M|   2177    ( 3 )|  00 : 0 
|    7  |      TABLE ACCESS FULL| T1   |  1000K|  3906K|       |    315    ( 5 )|  00 : 0 
|    8  |      TABLE ACCESS FULL| T2   |  1000K|  3906K|       |    315    ( 5 )|  00 : 0 
--------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
    4  - access("TT"."ID_1"="T3"."ID")
       filter("TT"."ID_1"="T3"."ID")
    6  - access("T1"."ID"="T2"."ID")