
With no_unnest, without push_subq


8 rows selected.


Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=ALL_ROWS (Cost=22 Card=7 Bytes=126)
   1    0   FILTER
   2    1     NESTED LOOPS (Cost=22 Card=7 Bytes=126)
   3    2       TABLE ACCESS (BY INDEX ROWID) OF 'PARENT' (Cost=4 Card=6 Bytes=54)
   4    3         INDEX (RANGE SCAN) OF 'PAR_PK' (UNIQUE) (Cost=2 Card=6)
   5    2       TABLE ACCESS (BY INDEX ROWID) OF 'CHILD' (Cost=3 Card=817 Bytes=7353)
   6    5         INDEX (RANGE SCAN) OF 'CHI_PK' (UNIQUE) (Cost=2 Card=817)
   7    1     TABLE ACCESS (BY INDEX ROWID) OF 'SUBTEST' (Cost=2 Card=1 Bytes=14)
   8    7       INDEX (UNIQUE SCAN) OF 'SUB_PK' (UNIQUE) (Cost=1 Card=1)




Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
       1224  consistent gets
          0  physical reads
          0  redo size
        654  bytes sent via SQL*Net to client
        425  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          3  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=22 Card=7 Bytes=126)
   1    0   NESTED LOOPS (Cost=22 Card=7 Bytes=126)
   2    1     TABLE ACCESS (BY INDEX ROWID) OF 'PARENT' (Cost=4 Card=6 Bytes=54)
   3    2       INDEX (RANGE SCAN) OF 'PAR_PK' (UNIQUE) (Cost=2 Card=6)
   4    2       TABLE ACCESS (BY INDEX ROWID) OF 'SUBTEST' (Cost=2 Card=1 Bytes=14)
   5    4         INDEX (UNIQUE SCAN) OF 'SUB_PK' (UNIQUE) (Cost=1 Card=1)
   6    1     TABLE ACCESS (BY INDEX ROWID) OF 'CHILD' (Cost=3 Card=817 Bytes=7353)
   7    6       INDEX (RANGE SCAN) OF 'CHI_PK' (UNIQUE) (Cost=2 Card=817)




Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
        320  consistent gets
          0  physical reads
          0  redo size
        654  bytes sent via SQL*Net to client
        425  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          3  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=22 Card=7 Bytes=126)
   1    0   NESTED LOOPS (Cost=22 Card=7 Bytes=126)
   2    1     TABLE ACCESS (BY INDEX ROWID) OF 'PARENT' (Cost=4 Card=6 Bytes=54)
   3    2       INDEX (RANGE SCAN) OF 'PAR_PK' (UNIQUE) (Cost=2 Card=6)
   4    2       TABLE ACCESS (BY INDEX ROWID) OF 'SUBTEST' (Cost=2 Card=1 Bytes=14)
   5    4         INDEX (UNIQUE SCAN) OF 'SUB_PK' (UNIQUE) (Cost=1 Card=1)
   6    1     TABLE ACCESS (BY INDEX ROWID) OF 'CHILD' (Cost=3 Card=817 Bytes=7353)
   7    6       INDEX (RANGE SCAN) OF 'CHI_PK' (UNIQUE) (Cost=2 Card=817)




Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
        320  consistent gets
          0  physical reads
          0  redo size
        654  bytes sent via SQL*Net to client
        425  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          3  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=22 Card=7 Bytes=126)
   1    0   FILTER
   2    1     NESTED LOOPS (Cost=22 Card=7 Bytes=126)
   3    2       TABLE ACCESS (BY INDEX ROWID) OF 'PARENT' (Cost=4 Card=6 Bytes=54)
   4    3         INDEX (RANGE SCAN) OF 'PAR_PK' (UNIQUE) (Cost=2 Card=6)
   5    2       TABLE ACCESS (BY INDEX ROWID) OF 'CHILD' (Cost=3 Card=817 Bytes=7353)
   6    5         INDEX (RANGE SCAN) OF 'CHI_PK' (UNIQUE) (Cost=2 Card=817)
   7    1     TABLE ACCESS (BY INDEX ROWID) OF 'SUBTEST' (Cost=2 Card=1 Bytes=14)
   8    7       INDEX (UNIQUE SCAN) OF 'SUB_PK' (UNIQUE) (Cost=1 Card=1)




Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
       1224  consistent gets
          0  physical reads
          0  redo size
        654  bytes sent via SQL*Net to client
        425  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          3  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=73 Card=89 Bytes=3366)
   1    0   HASH JOIN (SEMI) (Cost=73 Card=89 Bytes=3366)
   2    1     HASH JOIN (Cost=68 Card=116 Bytes=2088)
   3    2       TABLE ACCESS (BY INDEX ROWID) OF 'PARENT' (Cost=4 Card=103 Bytes=927)
   4    3         INDEX (RANGE SCAN) OF 'PAR_PK' (UNIQUE) (Cost=2 Card=103)
   5    2       TABLE ACCESS (FULL) OF 'CHILD' (Cost=63 Card=817 Bytes=7353)
   6    1     VIEW OF 'VW_SQ_1' (Cost=4 Card=90 Bytes=1800)
   7    6       TABLE ACCESS (BY INDEX ROWID) OF 'SUBTEST' (Cost=4 Card=90 Bytes=1260)
   8    7         INDEX (RANGE SCAN) OF 'SUB_PK' (UNIQUE) (Cost=2 Card=90)




Statistics
----------------------------------------------------------
          0  recursive calls
          6  db block gets
        420  consistent gets
          0  physical reads
          0  redo size
        654  bytes sent via SQL*Net to client
        425  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          4  sorts (memory)
          0  sorts (disk)
          8  rows processed

