
With no_unnest, without push_subq


8 rows selected.


Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=ALL_ROWS (Cost=183 Card=1 Bytes=18)
   1    0   FILTER
   2    1     HASH JOIN (Cost=68 Card=115 Bytes=2070)
   3    2       TABLE ACCESS (BY INDEX ROWID) OF 'PARENT' (TABLE) (Cost=4 Card=102 Bytes=918)
   4    3         INDEX (RANGE SCAN) OF 'PAR_PK' (INDEX (UNIQUE)) (Cost=2 Card=102)
   5    2       TABLE ACCESS (FULL) OF 'CHILD' (TABLE) (Cost=63 Card=816 Bytes=7344)
   6    1     TABLE ACCESS (BY INDEX ROWID) OF 'SUBTEST' (TABLE) (Cost=2 Card=1 Bytes=14)
   7    6       INDEX (UNIQUE SCAN) OF 'SUB_PK' (INDEX (UNIQUE)) (Cost=1 Card=1)




Statistics
----------------------------------------------------------
          1  recursive calls
          0  db block gets
        804  consistent gets
          0  physical reads
          0  redo size
        547  bytes sent via SQL*Net to client
        508  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          8  rows processed


With no_unnest, with push_subq


8 rows selected.


Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=ALL_ROWS (Cost=16 Card=6 Bytes=108)
   1    0   TABLE ACCESS (BY INDEX ROWID) OF 'CHILD' (TABLE) (Cost=2 Card=1 Bytes=9)
   2    1     NESTED LOOPS (Cost=14 Card=6 Bytes=108)
   3    2       TABLE ACCESS (BY INDEX ROWID) OF 'PARENT' (TABLE) (Cost=4 Card=5 Bytes=45)
   4    3         INDEX (RANGE SCAN) OF 'PAR_PK' (INDEX (UNIQUE)) (Cost=2 Card=102)
   5    3         TABLE ACCESS (BY INDEX ROWID) OF 'SUBTEST' (TABLE) (Cost=2 Card=1 Bytes=14)
   6    5           INDEX (UNIQUE SCAN) OF 'SUB_PK' (INDEX (UNIQUE)) (Cost=1 Card=1)
   7    2       INDEX (RANGE SCAN) OF 'CHI_PK' (INDEX (UNIQUE)) (Cost=1 Card=1)




Statistics
----------------------------------------------------------
          1  recursive calls
          0  db block gets
        320  consistent gets
          0  physical reads
          0  redo size
        547  bytes sent via SQL*Net to client
        508  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          8  rows processed


Without no_unnest, with push_subq


8 rows selected.


Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=ALL_ROWS (Cost=11 Card=1 Bytes=32)
   1    0   TABLE ACCESS (BY INDEX ROWID) OF 'CHILD' (TABLE) (Cost=2 Card=1 Bytes=9)
   2    1     NESTED LOOPS (Cost=11 Card=1 Bytes=32)
   3    2       HASH JOIN (SEMI) (Cost=9 Card=1 Bytes=23)
   4    3         TABLE ACCESS (BY INDEX ROWID) OF 'PARENT' (TABLE) (Cost=4 Card=102 Bytes=918)
   5    4           INDEX (RANGE SCAN) OF 'PAR_PK' (INDEX (UNIQUE)) (Cost=2 Card=102)
   6    3         TABLE ACCESS (BY INDEX ROWID) OF 'SUBTEST' (TABLE) (Cost=4 Card=91 Bytes=1274)
   7    6           INDEX (RANGE SCAN) OF 'SUB_PK' (INDEX (UNIQUE)) (Cost=2 Card=102)
   8    2       INDEX (RANGE SCAN) OF 'CHI_PK' (INDEX (UNIQUE)) (Cost=1 Card=1)




Statistics
----------------------------------------------------------
          1  recursive calls
          0  db block gets
         22  consistent gets
          0  physical reads
          0  redo size
        547  bytes sent via SQL*Net to client
        508  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          8  rows processed


Without no_unnest, without push_subq


8 rows selected.


Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=ALL_ROWS (Cost=11 Card=1 Bytes=32)
   1    0   TABLE ACCESS (BY INDEX ROWID) OF 'CHILD' (TABLE) (Cost=2 Card=1 Bytes=9)
   2    1     NESTED LOOPS (Cost=11 Card=1 Bytes=32)
   3    2       HASH JOIN (SEMI) (Cost=9 Card=1 Bytes=23)
   4    3         TABLE ACCESS (BY INDEX ROWID) OF 'PARENT' (TABLE) (Cost=4 Card=102 Bytes=918)
   5    4           INDEX (RANGE SCAN) OF 'PAR_PK' (INDEX (UNIQUE)) (Cost=2 Card=102)
   6    3         TABLE ACCESS (BY INDEX ROWID) OF 'SUBTEST' (TABLE) (Cost=4 Card=91 Bytes=1274)
   7    6           INDEX (RANGE SCAN) OF 'SUB_PK' (INDEX (UNIQUE)) (Cost=2 Card=102)
   8    2       INDEX (RANGE SCAN) OF 'CHI_PK' (INDEX (UNIQUE)) (Cost=1 Card=1)




Statistics
----------------------------------------------------------
          1  recursive calls
          0  db block gets
         22  consistent gets
          0  physical reads
          0  redo size
        547  bytes sent via SQL*Net to client
        508  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          8  rows processed


With explicit unnest, without push_subq
(Especially for 8i)


8 rows selected.


Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=ALL_ROWS (Cost=11 Card=1 Bytes=32)
   1    0   TABLE ACCESS (BY INDEX ROWID) OF 'CHILD' (TABLE) (Cost=2 Card=1 Bytes=9)
   2    1     NESTED LOOPS (Cost=11 Card=1 Bytes=32)
   3    2       HASH JOIN (SEMI) (Cost=9 Card=1 Bytes=23)
   4    3         TABLE ACCESS (BY INDEX ROWID) OF 'PARENT' (TABLE) (Cost=4 Card=102 Bytes=918)
   5    4           INDEX (RANGE SCAN) OF 'PAR_PK' (INDEX (UNIQUE)) (Cost=2 Card=102)
   6    3         TABLE ACCESS (BY INDEX ROWID) OF 'SUBTEST' (TABLE) (Cost=4 Card=91 Bytes=1274)
   7    6           INDEX (RANGE SCAN) OF 'SUB_PK' (INDEX (UNIQUE)) (Cost=2 Card=102)
   8    2       INDEX (RANGE SCAN) OF 'CHI_PK' (INDEX (UNIQUE)) (Cost=1 Card=1)




Statistics
----------------------------------------------------------
          1  recursive calls
          0  db block gets
         22  consistent gets
          0  physical reads
          0  redo size
        547  bytes sent via SQL*Net to client
        508  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          8  rows processed

