CBO对于Oracle SQL执行计划的影响(之一)

Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=CHOOSE (Cost=2057 Card=1 Bytes=288)
   1    0   NESTED LOOPS (Cost=2057 Card=1 Bytes=288)
   2    1     NESTED LOOPS (Cost=2056 Card=1 Bytes=256)
   3    2       NESTED LOOPS (Cost=2054 Card=1 Bytes=219)
   4    3         NESTED LOOPS (Cost=2053 Card=1 Bytes=178)
   5    4           NESTED LOOPS (Cost=2009 Card=1 Bytes=131)
   6    5             MERGE JOIN (Cost=2008 Card=1 Bytes=100)
   7    6               SORT (JOIN) (Cost=950 Card=36412 Bytes=1747776)
   8    7                 TABLE ACCESS (FULL) OF 'SP_CHK_SUB' (Cost=59 Card=36412 Bytes=1747776)
   9    6               SORT (JOIN) (Cost=1058 Card=36730 Bytes=1909960)
  10    9                 TABLE ACCESS (FULL) OF 'SP_RECEIVE_SUB' (Cost=89 Card=36730 Bytes=1909960)
  11    5             TABLE ACCESS (BY INDEX ROWID) OF 'SP_CHK' (Cost=1 Card=3870 Bytes=119970)
  12   11               INDEX (UNIQUE SCAN) OF 'PK_SP_CHK' (UNIQUE)
  13    4           TABLE ACCESS (FULL) OF 'SP_TRANS' (Cost=44 Card=1717 Bytes=80699)
  14    3         TABLE ACCESS (BY INDEX ROWID) OF 'SP_RECEIVE' (Cost=1 Card=7816 Bytes=320456)
  15   14           INDEX (UNIQUE SCAN) OF 'PK_SP_RECEIVE' (UNIQUE)
  16    2       TABLE ACCESS (BY INDEX ROWID) OF 'SP_TRANS_SUB' (Cost=2 Card=136371 Bytes=5045727)
  17   16         INDEX (UNIQUE SCAN) OF 'PK_SP_TRANS_SUB' (UNIQUE) (Cost=1 Card=136371)
  18    1     TABLE ACCESS (BY INDEX ROWID) OF 'SP_ITEM' (Cost=1 Card=29763 Bytes=952416)
  19   18       INDEX (UNIQUE SCAN) OF 'SYS_C0012193' (UNIQUE)
       

SQL>   SELECT "SP_TRANS"."TRANS_NO",   
  2           "SP_TRANS"."TRANS_TYPE",   
  3           "SP_TRANS"."STORE_NO",   
  4           "SP_TRANS"."BILL_NO",   
  5           "SP_TRANS"."TRANSDATE",   
  6           "SP_TRANS"."MANAGER_ID",   
  7           "SP_TRANS"."REMARK",   
  8           "SP_TRANS"."STATE",   
  9           "SP_TRANS_SUB"."TRANS_NO",   
 10           "SP_TRANS_SUB"."ITEM_CODE",   
 11           "SP_TRANS_SUB"."COUNTRY",   
 12           "SP_TRANS_SUB"."QTY",   
 13           "SP_TRANS_SUB"."PRICE",   
 14           "SP_TRANS_SUB"."TOTAL",   
 15           "SP_CHK"."CHK_NO",   
 16           "SP_CHK"."RECEIVE_NO",   
 17           "SP_CHK"."CHECKER",   
 18           "SP_CHK_SUB"."CHK_NO",   
 19           "SP_CHK_SUB"."ITEM_CODE",   
 20           "SP_CHK_SUB"."COUNTRY",   
 21           "SP_CHK_SUB"."PLAN_NO",   
 22           "SP_CHK_SUB"."PLAN_LINE",   
 23           "SP_CHK_SUB"."QTY_CHECKOUT",
 24           "SP_CHK_SUB"."NOW_QTY",   
 25           "SP_RECEIVE"."RECEIVE_NO",   
 26           "SP_RECEIVE"."VENDOR_NAME",   
 27           "SP_RECEIVE"."BUYER",   
 28           "SP_RECEIVE_SUB"."RECEIVE_NO",   
 29           "SP_RECEIVE_SUB"."PLAN_NO",   
 30           "SP_RECEIVE_SUB"."PLAN_LINE",   
 31           "SP_RECEIVE_SUB"."ITEM_NAME",   
 32           "SP_RECEIVE_SUB"."COUNTRY",
 33           "SP_ITEM"."ITEM_CODE",
 34           "SP_ITEM"."CHART_ID",
 35           "SP_ITEM"."SPECIFICATION"  
 36      FROM "SP_TRANS",
 37           "SP_TRANS_SUB",
 38           "SP_CHK",
 39           "SP_CHK_SUB",
 40           "SP_RECEIVE",
 41           "SP_RECEIVE_SUB",
 42           "SP_ITEM"
 43     WHERE ( "SP_TRANS_SUB"."TRANS_NO" = "SP_TRANS"."TRANS_NO" ) and
 44           ( "SP_TRANS"."BILL_NO" = "SP_CHK"."CHK_NO") and
 45           ( "SP_CHK_SUB"."CHK_NO" = "SP_CHK"."CHK_NO" ) and  
 46           ( "SP_CHK"."RECEIVE_NO" = "SP_RECEIVE"."RECEIVE_NO" ) and
 47           ( "SP_CHK"."STATE" = 15 ) and
 48           ( "SP_RECEIVE_SUB"."RECEIVE_NO" = "SP_RECEIVE"."RECEIVE_NO" ) and  
 49           ( "SP_TRANS_SUB"."ITEM_CODE" = "SP_ITEM"."ITEM_CODE" ) and
 50           ( "SP_TRANS_SUB"."ITEM_CODE" = "SP_CHK_SUB"."ITEM_CODE" ) and  
 51           ( "SP_CHK_SUB"."ITEM_CODE" = "SP_RECEIVE_SUB"."ITEM_CODE" ) and  
 52           ( "SP_CHK_SUB"."COUNTRY" = "SP_TRANS_SUB"."COUNTRY" ) and  
 53           ( "SP_CHK_SUB"."COUNTRY" = "SP_RECEIVE_SUB"."COUNTRY" ) and 
 54           ( "SP_CHK_SUB"."PLAN_NO" = "SP_RECEIVE_SUB"."PLAN_NO" ) and
 55           ( "SP_CHK_SUB"."PLAN_LINE" = "SP_RECEIVE_SUB"."PLAN_LINE" ) and
 56           (to_char("SP_TRANS"."TRANSDATE" ,'YYYY-MM-DD') >='2003-01-01')
 57  /
130 rows selected.
Elapsed:  00: 29: 1785.47
Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=CHOOSE (Cost=2057 Card=1 Bytes=288)
   1    0   NESTED LOOPS (Cost=2057 Card=1 Bytes=288)
   2    1     NESTED LOOPS (Cost=2056 Card=1 Bytes=256)
   3    2       NESTED LOOPS (Cost=2054 Card=1 Bytes=219)
   4    3         NESTED LOOPS (Cost=2053 Card=1 Bytes=178)
   5    4           NESTED LOOPS (Cost=2009 Card=1 Bytes=131)
   6    5             MERGE JOIN (Cost=2008 Card=1 Bytes=100)
   7    6               SORT (JOIN) (Cost=950 Card=36412 Bytes=1747776)
   8    7                 TABLE ACCESS (FULL) OF 'SP_CHK_SUB' (Cost=59 Card=36412 Bytes=1747776)
   9    6               SORT (JOIN) (Cost=1058 Card=36730 Bytes=1909960)
  10    9                 TABLE ACCESS (FULL) OF 'SP_RECEIVE_SUB' (Cost=89 Card=36730 Bytes=1909960)
  11    5             TABLE ACCESS (BY INDEX ROWID) OF 'SP_CHK' (Cost=1 Card=3870 Bytes=119970)
  12   11               INDEX (UNIQUE SCAN) OF 'PK_SP_CHK' (UNIQUE)
  13    4           TABLE ACCESS (FULL) OF 'SP_TRANS' (Cost=44 Card=1717 Bytes=80699)
  14    3         TABLE ACCESS (BY INDEX ROWID) OF 'SP_RECEIVE' (Cost=1 Card=7816 Bytes=320456)
  15   14           INDEX (UNIQUE SCAN) OF 'PK_SP_RECEIVE' (UNIQUE)
  16    2       TABLE ACCESS (BY INDEX ROWID) OF 'SP_TRANS_SUB' (Cost=2 Card=136371 Bytes=5045727)
  17   16         INDEX (UNIQUE SCAN) OF 'PK_SP_TRANS_SUB' (UNIQUE) (Cost=1 Card=136371)
  18    1     TABLE ACCESS (BY INDEX ROWID) OF 'SP_ITEM' (Cost=1 Card=29763 Bytes=952416)
  19   18       INDEX (UNIQUE SCAN) OF 'SYS_C0012193' (UNIQUE)
Statistics
----------------------------------------------------------
         16  recursive calls
     186307  db block gets
   10685361  consistent gets
       2329  physical reads
          0  redo size
      38486  bytes sent via SQL*Net to client
       1117  bytes received via SQL*Net from client
         10  SQL*Net roundtrips to/from client
          7  sorts (memory)
          2  sorts (disk)
        130  rows processed
      

Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=HINT: RULE
   1    0   NESTED LOOPS
   2    1     NESTED LOOPS
   3    2       NESTED LOOPS
   4    3         NESTED LOOPS
   5    4           NESTED LOOPS
   6    5             NESTED LOOPS
   7    6               TABLE ACCESS (FULL) OF 'SP_TRANS_SUB'
   8    6               TABLE ACCESS (BY INDEX ROWID) OF 'SP_ITEM'
   9    8                 INDEX (UNIQUE SCAN) OF 'SYS_C0012193' (UNIQUE)
  10    5             TABLE ACCESS (BY INDEX ROWID) OF 'SP_TRANS'
  11   10               INDEX (UNIQUE SCAN) OF 'PK_HSP_TRANS' (UNIQUE)
  12    4           TABLE ACCESS (BY INDEX ROWID) OF 'SP_CHK'
  13   12             INDEX (UNIQUE SCAN) OF 'PK_SP_CHK' (UNIQUE)
  14    3         TABLE ACCESS (BY INDEX ROWID) OF 'SP_RECEIVE'
  15   14           INDEX (UNIQUE SCAN) OF 'PK_SP_RECEIVE' (UNIQUE)
  16    2       TABLE ACCESS (BY INDEX ROWID) OF 'SP_CHK_SUB'
  17   16         INDEX (RANGE SCAN) OF 'IDX_CHK_SUB_ITEM_CODE' (NON-UNIQUE)
  18    1     TABLE ACCESS (BY INDEX ROWID) OF 'SP_RECEIVE_SUB'
  19   18       INDEX (UNIQUE SCAN) OF 'PK_SP_RECEIVE_SUB' (UNIQUE)
      

SQL>@sql
130 rows selected.
Elapsed:  00: 00: 12.17
Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=HINT: RULE
   1    0   NESTED LOOPS
   2    1     NESTED LOOPS
   3    2       NESTED LOOPS
   4    3         NESTED LOOPS
   5    4           NESTED LOOPS
   6    5             NESTED LOOPS
   7    6               TABLE ACCESS (FULL) OF 'SP_TRANS_SUB'
   8    6               TABLE ACCESS (BY INDEX ROWID) OF 'SP_ITEM'
   9    8                 INDEX (UNIQUE SCAN) OF 'SYS_C0012193' (UNIQUE)
  10    5             TABLE ACCESS (BY INDEX ROWID) OF 'SP_TRANS'
  11   10               INDEX (UNIQUE SCAN) OF 'PK_HSP_TRANS' (UNIQUE)
  12    4           TABLE ACCESS (BY INDEX ROWID) OF 'SP_CHK'
  13   12             INDEX (UNIQUE SCAN) OF 'PK_SP_CHK' (UNIQUE)
  14    3         TABLE ACCESS (BY INDEX ROWID) OF 'SP_RECEIVE'
  15   14           INDEX (UNIQUE SCAN) OF 'PK_SP_RECEIVE' (UNIQUE)
  16    2       TABLE ACCESS (BY INDEX ROWID) OF 'SP_CHK_SUB'
  17   16         INDEX (RANGE SCAN) OF 'IDX_CHK_SUB_ITEM_CODE' (NON-UNIQUE)
  18    1     TABLE ACCESS (BY INDEX ROWID) OF 'SP_RECEIVE_SUB'
  19   18       INDEX (UNIQUE SCAN) OF 'PK_SP_RECEIVE_SUB' (UNIQUE)
Statistics
----------------------------------------------------------
          0  recursive calls
          6  db block gets
     829182  consistent gets
          0  physical reads
          0  redo size
      37383  bytes sent via SQL*Net to client
       1127  bytes received via SQL*Net from client
         10  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
        130  rows processed
SQL> 
      

SQL>@sql
已选择130行。
已用时间:  00: 00: 05.67
Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=CHOOSE (Cost=3284 Card=1 Bytes=288)
   1    0   NESTED LOOPS (Cost=3284 Card=1 Bytes=288)
   2    1     NESTED LOOPS (Cost=3283 Card=1 Bytes=256)
   3    2       MERGE JOIN (Cost=3282 Card=1 Bytes=204)
   4    3         SORT (JOIN) (Cost=2333 Card=6823 Bytes=1064388)
   5    4           HASH JOIN (Cost=1848 Card=6823 Bytes=1064388)
   6    5             HASH JOIN (Cost=216 Card=1717 Bytes=204323)
   7    6               HASH JOIN (Cost=96 Card=1717 Bytes=133926)
   8    7                 TABLE ACCESS (FULL) OF 'SP_TRANS' (Cost=44 Card=1717 Bytes=80699)
   9    7                 TABLE ACCESS (FULL) OF 'SP_CHK' (Cost=13 Card=3870 Bytes=119970)
  10    6               TABLE ACCESS (FULL) OF 'SP_RECEIVE' (Cost=17 Card=7816 Bytes=320456)
  11    5             TABLE ACCESS (FULL) OF 'SP_TRANS_SUB' (Cost=155 Card=136371 Bytes=5045727)
  12    3         SORT (JOIN) (Cost=950 Card=36412 Bytes=1747776)
  13   12           TABLE ACCESS (FULL) OF 'SP_CHK_SUB' (Cost=59 Card=36412 Bytes=1747776)
  14    2       TABLE ACCESS (BY INDEX ROWID) OF 'SP_RECEIVE_SUB' (Cost=1 Card=36730 Bytes=1909960)
  15   14         INDEX (UNIQUE SCAN) OF 'PK_SP_RECEIVE_SUB' (UNIQUE)
  16    1     TABLE ACCESS (BY INDEX ROWID) OF 'SP_ITEM' (Cost=1 Card=29763 Bytes=952416)
  17   16       INDEX (UNIQUE SCAN) OF 'SYS_C0012193' (UNIQUE)
Statistics
----------------------------------------------------------
          8  recursive calls
         88  db block gets
       2667  consistent gets
       1093  physical reads
          0  redo size
      37285  bytes sent via SQL*Net to client
       1109  bytes received via SQL*Net from client
         10  SQL*Net roundtrips to/from client
          8  sorts (memory)
          1  sorts (disk)
        130  rows processed
SQL>