Oracle 执行计划选择疑问，求解释

xclwps

在贴出27日的sql_A 的10053 内容就应该可以说明了：
****************
QUERY BLOCK TEXT
****************
select v.emp_id,ps.organ_alias,po.organ_id,po.in_use
  from emp_basicinfo v,pub_stru_new ps,pub_organ_new po
where  ps.organ_id = po.organ_id
   and v.organ_id = ps.organ_id
   and ps.in_use= '1'
   and ps.stru_path like '1#03S000000000000000000000000000#03S990000000000000000000000000%'
   
*********************
QUERY BLOCK SIGNATURE
*********************
qb name was generated
signature (optimizer): qb_name=SEL$1 nbfros=3 flg=0
  fro(0): flg=0 objn=196377 hint_alias="PO"@"SEL$1"
  fro(1): flg=0 objn=196376 hint_alias="PS"@"SEL$1"
  fro(2): flg=0 objn=136265 hint_alias="V"@"SEL$1"
*****************************
SYSTEM STATISTICS INFORMATION
*****************************
  Using NOWORKLOAD Stats
  CPUSPEED: 485 millions instruction/sec
  IOTFRSPEED: 4096 bytes per millisecond (default is 4096)
  IOSEEKTIM: 10 milliseconds (default is 10)
***************************************
BASE STATISTICAL INFORMATION
***********************
Table Stats::
  Table: PUB_ORGAN_NEW  Alias: PO
    #Rows: 17714  #Blks:  260  AvgRowLen:  94.00
  Column (#1): ORGAN_ID(VARCHAR2)
    AvgLen: 31.00 NDV: 17714 Nulls: 0 Density: 5.6453e-005
Index Stats::
  Index: ORGAN_ID_NEW_KEY  Col#: 1
    LVLS: 1  #LB: 102  #DK: 17759  LB/K: 1.00  DB/K: 1.00  CLUF: 14436.00
***********************
Table Stats::
  Table: PUB_STRU_NEW  Alias: PS  (NOT ANALYZED)
    #Rows: 75473  #Blks:  924  AvgRowLen:  100.00
  Column (#2): ORGAN_ID(VARCHAR2)  NO STATISTICS (using defaults)
    AvgLen: 30.00 NDV: 2359 Nulls: 0 Density: 4.2399e-004
Index Stats::
  Index: STRU_NEW_ID  Col#: 1
    LVLS: 1  #LB: 102  #DK: 17758  LB/K: 1.00  DB/K: 1.00  CLUF: 15586.00
  Index: STRU_ORGAN_ID_NEW  Col#: 9 16
    LVLS: 2  #LB: 416  #DK: 17758  LB/K: 1.00  DB/K: 1.00  CLUF: 16655.00
***********************
Table Stats::
  Table: EMP_BASICINFO  Alias:  V
    #Rows: 135666  #Blks:  5730  AvgRowLen:  292.00
  Column (#25): ORGAN_ID(VARCHAR2)
    AvgLen: 30.00 NDV: 13564 Nulls: 0 Density: 7.3725e-005
Index Stats::
  Index: BIN$7RivMVmQR9mDS0ps9uL0Pg==$0  Col#: 32
    LVLS: 1  #LB: 364  #DK: 4  LB/K: 91.00  DB/K: 2418.00  CLUF: 9673.00
  Index: BIN$9tpDYkpCQ4i+HxKnv7IOZw==$0  Col#: 64
    LVLS: 1  #LB: 261  #DK: 6  LB/K: 43.00  DB/K: 1581.00  CLUF: 9488.00
  Index: BIN$CNVAVQhzSXaB1FToqJRxFQ==$0  Col#: 57
    LVLS: 1  #LB: 414  #DK: 87349  LB/K: 1.00  DB/K: 1.00  CLUF: 135405.00
  Index: BIN$DrS+tmFNQqK016vCf8DGwQ==$0  Col#: 16
    LVLS: 2  #LB: 759  #DK: 135666  LB/K: 1.00  DB/K: 1.00  CLUF: 84132.00
  Index: BIN$G6vBtTx7TZKzXLgOFIRehw==$0  Col#: 56
    LVLS: 1  #LB: 335  #DK: 88087  LB/K: 1.00  DB/K: 1.00  CLUF: 135516.00
  Index: BIN$GS03P7E5QVmQCV3nfyXyhg==$0  Col#: 23 48
    LVLS: 2  #LB: 714  #DK: 115915  LB/K: 1.00  DB/K: 1.00  CLUF: 134687.00
  Index: BIN$L/DKnLu5RSKbqD2X9oGkRg==$0  Col#: 52
    LVLS: 2  #LB: 428  #DK: 13516  LB/K: 1.00  DB/K: 6.00  CLUF: 92557.00
  Index: BIN$RSKsdMCyRse6YkrKA2minA==$0  Col#: 58
    LVLS: 1  #LB: 258  #DK: 4  LB/K: 64.00  DB/K: 3111.00  CLUF: 12446.00
  Index: BIN$Rd+jzN3sTV+Iz5P7J1pcgA==$0  Col#: 55
    LVLS: 1  #LB: 334  #DK: 2  LB/K: 167.00  DB/K: 4818.00  CLUF: 9637.00
  Index: BIN$RvxIUuiYRIKJSSczhjII9w==$0  Col#: 25
    LVLS: 2  #LB: 830  #DK: 13564  LB/K: 1.00  DB/K: 6.00  CLUF: 92591.00
  Index: BIN$gpXnQ2+4TGuSr/qYyba8LQ==$0  Col#: 45
    LVLS: 1  #LB: 261  #DK: 7  LB/K: 37.00  DB/K: 1171.00  CLUF: 8203.00
  Index: BIN$lEJnj0tOSDiAJycUiQAIMg==$0  Col#: 16 32
    LVLS: 2  #LB: 937  #DK: 135666  LB/K: 1.00  DB/K: 1.00  CLUF: 84132.00
  Index: BIN$mOg4zCnKR2WIMZ6FgCVoBA==$0  Col#: 59
    LVLS: 1  #LB: 263  #DK: 8  LB/K: 32.00  DB/K: 1062.00  CLUF: 8502.00
  Index: BIN$n+pvTsdEReCjXZXextjc8g==$0  Col#: 60
    LVLS: 1  #LB: 276  #DK: 22  LB/K: 12.00  DB/K: 313.00  CLUF: 6897.00
  Index: BIN$nip5iMcRR9ix2sUKDY4qMQ==$0  Col#: 62
    LVLS: 1  #LB: 255  #DK: 2  LB/K: 127.00  DB/K: 4717.00  CLUF: 9435.00
  Index: BIN$r8LrHwzUTXWTM5GNWzrpYg==$0  Col#: 63
    LVLS: 1  #LB: 262  #DK: 6  LB/K: 43.00  DB/K: 2958.00  CLUF: 17752.00
  Index: BIN$voLaxKDBShWZD1PoAgi+/g==$0  Col#: 61
    LVLS: 1  #LB: 260  #DK: 5  LB/K: 52.00  DB/K: 2117.00  CLUF: 10589.00
  Index: BIN$wpP6YgfdQ4KZS9WtratE1A==$0  Col#: 6
    LVLS: 1  #LB: 255  #DK: 2  LB/K: 127.00  DB/K: 4717.00  CLUF: 9435.00
***************************************
SINGLE TABLE ACCESS PATH         
  Table: EMP_BASICINFO  Alias: V     
    Card: Original: 135666  Rounded: 135666  Computed: 135666.00  Non Adjusted: 135666.00
  Access Path: TableScan
    Cost:  1276.70  Resp: 1276.70  Degree: 0
      Cost_io: 1255.00  Cost_cpu: 126275431
      Resp_io: 1255.00  Resp_cpu: 126275431
******** Begin index join costing ********
  ****** trying bitmap/domain indexes ******
  Access Path: index (FullScan)
    Index: BIN$9tpDYkpCQ4i+HxKnv7IOZw==$0
    resc_io: 262.00  resc_cpu: 28999017
    ix_sel: 1  ix_sel_with_filters: 1
    Cost: 266.98  Resp: 266.98  Degree: 0
  Access Path: index (FullScan)
    Index: BIN$CNVAVQhzSXaB1FToqJRxFQ==$0
    resc_io: 415.00  resc_cpu: 30088598
    ix_sel: 1  ix_sel_with_filters: 1
    Cost: 420.17  Resp: 420.17  Degree: 0
  Access Path: index (FullScan)
    Index: BIN$DrS+tmFNQqK016vCf8DGwQ==$0
    resc_io: 761.00  resc_cpu: 32552616
    ix_sel: 1  ix_sel_with_filters: 1
    Cost: 766.59  Resp: 766.59  Degree: 0
  Access Path: index (FullScan)
    Index: BIN$G6vBtTx7TZKzXLgOFIRehw==$0
    resc_io: 336.00  resc_cpu: 29526004
    ix_sel: 1  ix_sel_with_filters: 1
    Cost: 341.07  Resp: 341.07  Degree: 0
  Access Path: index (FullScan)
    Index: BIN$RSKsdMCyRse6YkrKA2minA==$0
    resc_io: 259.00  resc_cpu: 28977653
    ix_sel: 1  ix_sel_with_filters: 1
    Cost: 263.98  Resp: 263.98  Degree: 0
  Access Path: index (FullScan)
    Index: BIN$Rd+jzN3sTV+Iz5P7J1pcgA==$0
    resc_io: 335.00  resc_cpu: 29518882
    ix_sel: 1  ix_sel_with_filters: 1
    Cost: 340.07  Resp: 340.07  Degree: 0
  Access Path: index (FullScan)
    Index: BIN$RvxIUuiYRIKJSSczhjII9w==$0
    resc_io: 832.00  resc_cpu: 33058238
    ix_sel: 1  ix_sel_with_filters: 1
    Cost: 837.68  Resp: 837.68  Degree: 0
  Access Path: index (FullScan)
    Index: BIN$lEJnj0tOSDiAJycUiQAIMg==$0
    resc_io: 939.00  resc_cpu: 33820232
    ix_sel: 1  ix_sel_with_filters: 1
    Cost: 944.81  Resp: 944.81  Degree: 0
  Access Path: index (FullScan)
    Index: BIN$mOg4zCnKR2WIMZ6FgCVoBA==$0
    resc_io: 264.00  resc_cpu: 29013260
    ix_sel: 1  ix_sel_with_filters: 1
    Cost: 268.99  Resp: 268.99  Degree: 0
  Access Path: index (FullScan)
    Index: BIN$n+pvTsdEReCjXZXextjc8g==$0
    resc_io: 277.00  resc_cpu: 29105839
    ix_sel: 1  ix_sel_with_filters: 1
    Cost: 282.00  Resp: 282.00  Degree: 0
  Access Path: index (FullScan)
    Index: BIN$nip5iMcRR9ix2sUKDY4qMQ==$0
    resc_io: 256.00  resc_cpu: 28956289
    ix_sel: 1  ix_sel_with_filters: 1
    Cost: 260.98  Resp: 260.98  Degree: 0
  Access Path: index (FullScan)
    Index: BIN$r8LrHwzUTXWTM5GNWzrpYg==$0
    resc_io: 263.00  resc_cpu: 29006139
    ix_sel: 1  ix_sel_with_filters: 1
    Cost: 267.98  Resp: 267.98  Degree: 0
  Access Path: index (FullScan)
    Index: BIN$voLaxKDBShWZD1PoAgi+/g==$0
    resc_io: 261.00  resc_cpu: 28991896
    ix_sel: 1  ix_sel_with_filters: 1
    Cost: 265.98  Resp: 265.98  Degree: 0
  Access Path: index (FullScan)
    Index: BIN$nip5iMcRR9ix2sUKDY4qMQ==$0
    resc_io: 256.00  resc_cpu: 28956289
    ix_sel: 1  ix_sel_with_filters: 1
    Cost: 260.98  Resp: 260.98  Degree: 0
  Access Path: index (FullScan)
    Index: BIN$DrS+tmFNQqK016vCf8DGwQ==$0
    resc_io: 761.00  resc_cpu: 32552616
    ix_sel: 1  ix_sel_with_filters: 1
    Cost: 766.59  Resp: 766.59  Degree: 0
  Access Path: index (FullScan)
    Index: BIN$RvxIUuiYRIKJSSczhjII9w==$0
    resc_io: 832.00  resc_cpu: 33058238
    ix_sel: 1  ix_sel_with_filters: 1
    Cost: 837.68  Resp: 837.68  Degree: 0
******** Cost index join ********
Index join: Considering index join to index BIN$DrS+tmFNQqK016vCf8DGwQ==$0
Index join: Joining index BIN$RvxIUuiYRIKJSSczhjII9w==$0
Ix HA Join
  Outer table:
    resc: 958.24  card 135666.00  bytes: 40  deg: 1  resp: 958.24
  Inner table: <no name>
    resc: 1047.10  card: 135666.00  bytes: 40  deg: 1  resp: 1047.10
    using dmeth: 2  #groups: 1
    Cost per ptn: 677.87  #ptns: 1
    hash_area: 0 (max=0)   Hash join: Resc: 2683.22  Resp: 2683.22  [multiMatchCost=0.00]
******** Index join cost ********
Cost: 2683.22  
******** End index join costing ********
  Best:: AccessPath: TableScan
         Cost: 1276.70  Degree: 1  Resp: 1276.70  Card: 135666.00  Bytes: 0
***************************************
SINGLE TABLE ACCESS PATH
*** 2012-06-27 15:11:01.859
** Performing dynamic sampling initial checks. **
  Column (#9): STRU_PATH(VARCHAR2)  NO STATISTICS (using defaults)
    AvgLen: 1000.00 NDV: 2359 Nulls: 0 Density: 4.2399e-004
  Column (#16): IN_USE(CHARACTER)  NO STATISTICS (using defaults)
    AvgLen: 1.00 NDV: 2359 Nulls: 0 Density: 4.2399e-004
** Dynamic sampling initial checks returning TRUE (level = 2).
** Dynamic sampling updated index stats.: STRU_ORGAN_ID_NEW, blocks=441
** Dynamic sampling updated table stats.: blocks=924
*** 2012-06-27 15:11:01.859
** Generated dynamic sampling query:
    query text :
SELECT /* OPT_DYN_SAMP */ /*+ ALL_ROWS IGNORE_WHERE_CLAUSE NO_PARALLEL(SAMPLESUB) opt_param('parallel_execution_enabled', 'false') NO_PARALLEL_INDEX(SAMPLESUB) NO_SQL_TUNE */ NVL(SUM(C1),0), NVL(SUM(C2),0), NVL(SUM(C3),0), COUNT(DISTINCT C4), NVL(SUM(CASE WHEN C4 IS NULL THEN 1 ELSE 0 END),0) FROM (SELECT /*+ IGNORE_WHERE_CLAUSE NO_PARALLEL("PS") FULL("PS") NO_PARALLEL_INDEX("PS") */ 1 AS C1, CASE WHEN "PS"."IN_USE"='1' AND "PS"."STRU_PATH" LIKE '1#03S000000000000000000000000000#03S990000000000000000000000000%' THEN 1 ELSE 0 END AS C2, CASE WHEN "PS"."STRU_PATH" LIKE '1#03S000000000000000000000000000#03S990000000000000000000000000%' THEN 1 ELSE 0 END AS C3, "PS"."ORGAN_ID" AS C4 FROM "PUB_STRU_NEW" SAMPLE BLOCK (6.818182 , 1) SEED (1) "PS") SAMPLESUB
*** 2012-06-27 15:11:01.859
** Executed dynamic sampling query:
    level : 2
    sample pct. : 6.818182
    actual sample size : 1245
    filtered sample card. : 7
    filtered sample card. (index STRU_ORGAN_ID_NEW): 7
    orig. card. : 75473
    block cnt. table stat. : 924
    block cnt. for sampling: 924
    max. sample block cnt. : 64
    sample block cnt. : 63
    ndv C3 : 1245
        scaled : 18260.00
    nulls C4 : 0
        scaled : 0.00
    min. sel. est. : 0.00050000
** Dynamic sampling col. stats.:
  Column (#2): ORGAN_ID(VARCHAR2)  Part#: 0
    AvgLen: 30.00 NDV: 18260 Nulls: 0 Density: 5.4765e-005
** Using dynamic sampling NULLs estimates.
** Using dynamic sampling NDV estimates.
   Scaled NDVs using cardinality = 18260.
    index STRU_ORGAN_ID_NEW selectivity est.: 0.00562249
** Using dynamic sampling card. : 18260
** Dynamic sampling updated table card.
** Using single table dynamic sel. est. : 0.00562249
  Table: PUB_STRU_NEW  Alias: PS     
    Card: Original: 18260  Rounded: 103  Computed: 102.67  Non Adjusted: 102.67
  Access Path: TableScan
    Cost:  206.90  Resp: 206.90  Degree: 0
      Cost_io: 204.00  Cost_cpu: 16851461
      Resp_io: 204.00  Resp_cpu: 16851461
kkofmx: index filter:"PS"."IN_USE"='1' AND "PS"."STRU_PATH" LIKE '1#03S000000000000000000000000000#03S990000000000000000000000000%'
kkofmx: index filter:"PS"."STRU_PATH" LIKE '1#03S000000000000000000000000000#03S990000000000000000000000000%'
  Access Path: index (RangeScan)
    Index: STRU_ORGAN_ID_NEW
    resc_io: 6.00  resc_cpu: 63179
    ix_sel: 0.0056225  ix_sel_with_filters: 2.2490e-005
    Cost: 6.01  Resp: 6.01  Degree: 1
  Best:: AccessPath: IndexRange  Index: STRU_ORGAN_ID_NEW
         Cost: 6.01  Degree: 1  Resp: 6.01  Card: 102.67  Bytes: 0
***************************************
SINGLE TABLE ACCESS PATH         这个其实并不能最终决定扫描方式，因为在表 链接cost 计算的时候还会重新计算。
  Table: PUB_ORGAN_NEW  Alias: PO     
    Card: Original: 17714  Rounded: 17714  Computed: 17714.00  Non Adjusted: 17714.00
  Access Path: TableScan
    Cost:  60.32  Resp: 60.32  Degree: 0
      Cost_io: 59.00  Cost_cpu: 7697194
      Resp_io: 59.00  Resp_cpu: 7697194
  Best:: AccessPath: TableScan
         Cost: 60.32  Degree: 1  Resp: 60.32  Card: 17714.00  Bytes: 0
***************************************

OPTIMIZER STATISTICS AND COMPUTATIONS
***************************************
GENERAL PLANS
***************************************
Considering cardinality-based initial join order.
***********************
Join order[1]:  PUB_STRU_NEW[PS]#0  PUB_ORGAN_NEW[PO]#1  EMP_BASICINFO[V]#2
***************
Now joining: PUB_ORGAN_NEW[PO]#1
***************
NL Join
  Outer table: Card: 102.67  Cost: 6.01  Resp: 6.01  Degree: 1  Bytes: 564
  Inner table: PUB_ORGAN_NEW  Alias: PO
  Access Path: TableScan
    NL Join:  Cost: 6002.24  Resp: 6002.24  Degree: 0
      Cost_io: 5866.00  Cost_cpu: 792874202
      Resp_io: 5866.00  Resp_cpu: 792874202
  Access Path: index (UniqueScan)
    Index: ORGAN_ID_NEW_KEY
    resc_io: 1.00  resc_cpu: 9351
    ix_sel: 5.6453e-005  ix_sel_with_filters: 5.6453e-005
    NL Join: Cost: 106.09  Resp: 106.09  Degree: 1
      Cost_io: 105.92  Cost_cpu: 1003428
      Resp_io: 105.92  Resp_cpu: 1003428
  Access Path: index (AllEqUnique)
    Index: ORGAN_ID_NEW_KEY
    resc_io: 1.00  resc_cpu: 9351
    ix_sel: 5.6453e-005  ix_sel_with_filters: 5.6453e-005
    NL Join: Cost: 106.09  Resp: 106.09  Degree: 1
      Cost_io: 105.92  Cost_cpu: 1003428
      Resp_io: 105.92  Resp_cpu: 1003428
  Best NL cost: 106.09
          resc: 106.09 resc_io: 105.92 resc_cpu: 1003428
          resp: 106.09 resp_io: 105.92 resp_cpu: 1003428
Join Card:  102.67 = outer (102.67) * inner (17714.00) * sel (5.6453e-005)
Join Card - Rounded: 103 Computed: 102.67
SM Join
  Outer table:
    resc: 6.01  card 102.67  bytes: 564  deg: 1  resp: 6.01
  Inner table: PUB_ORGAN_NEW  Alias: PO
    resc: 60.32  card: 17714.00  bytes: 33  deg: 1  resp: 60.32
    using dmeth: 2  #groups: 1
    SORT resource      Sort statistics
      Sort width:         231 Area size:      202752 Max Area size:    40684544
      Degree:               1
      Blocks to Sort:       8 Row size:          631 Total Rows:            103
      Initial runs:         1 Merge passes:        0 IO Cost / pass:          0
      Total IO sort cost: 0      Total CPU sort cost: 5850729
      Total Temp space used: 0
    SORT resource      Sort statistics
      Sort width:         231 Area size:      202752 Max Area size:    40684544
      Degree:               1
      Blocks to Sort:     102 Row size:           47 Total Rows:          17714
      Initial runs:         2 Merge passes:        1 IO Cost / pass:         56
      Total IO sort cost: 158      Total CPU sort cost: 19594771
      Total Temp space used: 1582000
  SM join: Resc: 228.71  Resp: 228.71  [multiMatchCost=0.00]
  SM cost: 228.71
     resc: 228.71 resc_io: 223.00 resc_cpu: 33205873
     resp: 228.71 resp_io: 223.00 resp_cpu: 33205873
HA Join
  Outer table:
    resc: 6.01  card 102.67  bytes: 564  deg: 1  resp: 6.01
  Inner table: PUB_ORGAN_NEW  Alias: PO
    resc: 60.32  card: 17714.00  bytes: 33  deg: 1  resp: 60.32
    using dmeth: 2  #groups: 1
    Cost per ptn: 0.81  #ptns: 1
    hash_area: 0 (max=0)   Hash join: Resc: 67.14  Resp: 67.14  [multiMatchCost=0.00]
  HA cost: 67.14
     resc: 67.14 resc_io: 65.00 resc_cpu: 12457073
     resp: 67.14 resp_io: 65.00 resp_cpu: 12457073
Best:: JoinMethod: Hash
       Cost: 67.14  Degree: 1  Resp: 67.14  Card: 102.67  Bytes: 597
***************
Now joining: EMP_BASICINFO[V]#2
***************
NL Join
  Outer table: Card: 102.67  Cost: 67.14  Resp: 67.14  Degree: 1  Bytes: 597
  Inner table: EMP_BASICINFO  Alias: V
  Access Path: TableScan
    NL Join:  Cost: 131408.03  Resp: 131408.03  Degree: 0
      Cost_io: 129171.00  Cost_cpu: 13018826486
      Resp_io: 129171.00  Resp_cpu: 13018826486
  Access Path: index (AllEqJoinGuess)
    Index: BIN$RvxIUuiYRIKJSSczhjII9w==$0
    resc_io: 9.00  resc_cpu: 74073
    ix_sel: 7.3725e-005  ix_sel_with_filters: 7.3725e-005
    NL Join: Cost: 809.77  Resp: 809.77  Degree: 1
      Cost_io: 806.58  Cost_cpu: 18582544
      Resp_io: 806.58  Resp_cpu: 18582544
  Best NL cost: 809.77
          resc: 809.77 resc_io: 806.58 resc_cpu: 18582544
          resp: 809.77 resp_io: 806.58 resp_cpu: 18582544
Join Card:  1026.86 = outer (102.67) * inner (135666.00) * sel (7.3725e-005)
Join Card - Rounded: 1027 Computed: 1026.86
SM Join
  Outer table:
    resc: 67.14  card 102.67  bytes: 597  deg: 1  resp: 67.14
  Inner table: EMP_BASICINFO  Alias: V
    resc: 1276.70  card: 135666.00  bytes: 60  deg: 1  resp: 1276.70
    using dmeth: 2  #groups: 1
    SORT resource      Sort statistics
      Sort width:         231 Area size:      202752 Max Area size:    40684544
      Degree:               1
      Blocks to Sort:       9 Row size:          667 Total Rows:            103
      Initial runs:         1 Merge passes:        0 IO Cost / pass:          0
      Total IO sort cost: 0      Total CPU sort cost: 5850729
      Total Temp space used: 0
    SORT resource      Sort statistics
      Sort width:         231 Area size:      202752 Max Area size:    40684544
      Degree:               1
      Blocks to Sort:    1263 Row size:           76 Total Rows:         135666
      Initial runs:         2 Merge passes:        1 IO Cost / pass:        686
      Total IO sort cost: 1949      Total CPU sort cost: 141136459
      Total Temp space used: 18539000
  SM join: Resc: 3318.10  Resp: 3318.10  [multiMatchCost=0.00]
  SM cost: 3318.10
     resc: 3318.10 resc_io: 3269.00 resc_cpu: 285719692
     resp: 3318.10 resp_io: 3269.00 resp_cpu: 285719692
HA Join
  Outer table:
    resc: 67.14  card 102.67  bytes: 597  deg: 1  resp: 67.14
  Inner table: EMP_BASICINFO  Alias: V
    resc: 1276.70  card: 135666.00  bytes: 60  deg: 1  resp: 1276.70
    using dmeth: 2  #groups: 1
    Cost per ptn: 2.83  #ptns: 1
    hash_area: 0 (max=0)   Hash join: Resc: 1346.67  Resp: 1346.67  [multiMatchCost=0.00]
  HA cost: 1346.67
     resc: 1346.67 resc_io: 1320.00 resc_cpu: 155224404
     resp: 1346.67 resp_io: 1320.00 resp_cpu: 155224404
Best:: JoinMethod: NestedLoop
       Cost: 809.77  Degree: 1  Resp: 809.77  Card: 1026.86  Bytes: 657
***********************
Best so far: Table#: 0  cost: 6.0109  card: 102.6667  bytes: 58092
             Table#: 1  cost: 67.1405  card: 102.6667  bytes: 61491
             Table#: 2  cost: 809.7705  card: 1026.8635  bytes: 674739
***********************
Join order[2]:  PUB_STRU_NEW[PS]#0  EMP_BASICINFO[V]#2  PUB_ORGAN_NEW[PO]#1
***************
Now joining: EMP_BASICINFO[V]#2
***************
NL Join
  Outer table: Card: 102.67  Cost: 6.01  Resp: 6.01  Degree: 1  Bytes: 564
  Inner table: EMP_BASICINFO  Alias: V
  Access Path: TableScan
    NL Join:  Cost: 131346.90  Resp: 131346.90  Degree: 0
      Cost_io: 129112.00  Cost_cpu: 13006432592
      Resp_io: 129112.00  Resp_cpu: 13006432592
  Access Path: index (AllEqJoinGuess)
    Index: BIN$RvxIUuiYRIKJSSczhjII9w==$0
    resc_io: 9.00  resc_cpu: 74073
    ix_sel: 7.3725e-005  ix_sel_with_filters: 7.3725e-005
    NL Join: Cost: 748.64  Resp: 748.64  Degree: 1
      Cost_io: 747.58  Cost_cpu: 6188650
      Resp_io: 747.58  Resp_cpu: 6188650
  Best NL cost: 748.64
          resc: 748.64 resc_io: 747.58 resc_cpu: 6188650
          resp: 748.64 resp_io: 747.58 resp_cpu: 6188650
Join Card:  1026.86 = outer (102.67) * inner (135666.00) * sel (7.3725e-005)
Join Card - Rounded: 1027 Computed: 1026.86
SM Join
  Outer table:
    resc: 6.01  card 102.67  bytes: 564  deg: 1  resp: 6.01
  Inner table: EMP_BASICINFO  Alias: V
    resc: 1276.70  card: 135666.00  bytes: 60  deg: 1  resp: 1276.70
    using dmeth: 2  #groups: 1
    SORT resource      Sort statistics
      Sort width:         231 Area size:      202752 Max Area size:    40684544
      Degree:               1
      Blocks to Sort:       8 Row size:          631 Total Rows:            103
      Initial runs:         1 Merge passes:        0 IO Cost / pass:          0
      Total IO sort cost: 0      Total CPU sort cost: 5850729
      Total Temp space used: 0
    SORT resource      Sort statistics
      Sort width:         231 Area size:      202752 Max Area size:    40684544
      Degree:               1
      Blocks to Sort:    1263 Row size:           76 Total Rows:         135666
      Initial runs:         2 Merge passes:        1 IO Cost / pass:        686
      Total IO sort cost: 1949      Total CPU sort cost: 141136459
      Total Temp space used: 18539000
  SM join: Resc: 3256.97  Resp: 3256.97  [multiMatchCost=0.00]
  SM cost: 3256.97
     resc: 3256.97 resc_io: 3210.00 resc_cpu: 273325798
     resp: 3256.97 resp_io: 3210.00 resp_cpu: 273325798
HA Join
  Outer table:
    resc: 6.01  card 102.67  bytes: 564  deg: 1  resp: 6.01
  Inner table: EMP_BASICINFO  Alias: V
    resc: 1276.70  card: 135666.00  bytes: 60  deg: 1  resp: 1276.70
    using dmeth: 2  #groups: 1
    Cost per ptn: 2.83  #ptns: 1
    hash_area: 0 (max=0)   Hash join: Resc: 1285.54  Resp: 1285.54  [multiMatchCost=0.00]
  HA cost: 1285.54
     resc: 1285.54 resc_io: 1261.00 resc_cpu: 142830510
     resp: 1285.54 resp_io: 1261.00 resp_cpu: 142830510
Best:: JoinMethod: NestedLoop
       Cost: 748.64  Degree: 1  Resp: 748.64  Card: 1026.86  Bytes: 624
***************
Now joining: PUB_ORGAN_NEW[PO]#1
***************
NL Join
  Outer table: Card: 1026.86  Cost: 748.64  Resp: 748.64  Degree: 1  Bytes: 624
  Inner table: PUB_ORGAN_NEW  Alias: PO
  Access Path: TableScan
    NL Join:  Cost: 60518.96  Resp: 60518.96  Degree: 0
      Cost_io: 59159.58  Cost_cpu: 7911207298
      Resp_io: 59159.58  Resp_cpu: 7911207298
  Access Path: index (UniqueScan)
    Index: ORGAN_ID_NEW_KEY
    resc_io: 1.00  resc_cpu: 9351
    ix_sel: 5.6453e-005  ix_sel_with_filters: 5.6453e-005
    NL Join: Cost: 1746.54  Resp: 1746.54  Degree: 1
      Cost_io: 1743.87  Cost_cpu: 15563754
      Resp_io: 1743.87  Resp_cpu: 15563754
  Access Path: index (AllEqUnique)
    Index: ORGAN_ID_NEW_KEY
    resc_io: 1.00  resc_cpu: 9351
    ix_sel: 5.6453e-005  ix_sel_with_filters: 5.6453e-005
    NL Join: Cost: 1746.54  Resp: 1746.54  Degree: 1
      Cost_io: 1743.87  Cost_cpu: 15563754
      Resp_io: 1743.87  Resp_cpu: 15563754
  Best NL cost: 1746.54
          resc: 1746.54 resc_io: 1743.87 resc_cpu: 15563754
          resp: 1746.54 resp_io: 1743.87 resp_cpu: 15563754
Join Card:  1026.86 = outer (1026.86) * inner (17714.00) * sel (5.6453e-005)
Join Card - Rounded: 1027 Computed: 1026.86
SM Join
  Outer table:
    resc: 748.64  card 1026.86  bytes: 624  deg: 1  resp: 748.64
  Inner table: PUB_ORGAN_NEW  Alias: PO
    resc: 60.32  card: 17714.00  bytes: 33  deg: 1  resp: 60.32
    using dmeth: 2  #groups: 1
    SORT resource      Sort statistics
      Sort width:         231 Area size:      202752 Max Area size:    40684544
      Degree:               1
      Blocks to Sort:      88 Row size:          697 Total Rows:           1027
      Initial runs:         2 Merge passes:        1 IO Cost / pass:         48
      Total IO sort cost: 136      Total CPU sort cost: 8449693
      Total Temp space used: 1418000
    SORT resource      Sort statistics
      Sort width:         231 Area size:      202752 Max Area size:    40684544
      Degree:               1
      Blocks to Sort:     102 Row size:           47 Total Rows:          17714
      Initial runs:         2 Merge passes:        1 IO Cost / pass:         56
      Total IO sort cost: 158      Total CPU sort cost: 19594771
      Total Temp space used: 1582000
  SM join: Resc: 1107.78  Resp: 1107.78  [multiMatchCost=0.00]
  SM cost: 1107.78
     resc: 1107.78 resc_io: 1100.58 resc_cpu: 41930309
     resp: 1107.78 resp_io: 1100.58 resp_cpu: 41930309
HA Join
  Outer table:
    resc: 748.64  card 1026.86  bytes: 624  deg: 1  resp: 748.64
  Inner table: PUB_ORGAN_NEW  Alias: PO
    resc: 60.32  card: 17714.00  bytes: 33  deg: 1  resp: 60.32
    using dmeth: 2  #groups: 1
    Cost per ptn: 0.83  #ptns: 1
    hash_area: 0 (max=0)   Hash join: Resc: 809.79  Resp: 809.79  [multiMatchCost=0.00]
  HA cost: 809.79
     resc: 809.79 resc_io: 806.58 resc_cpu: 18721144
     resp: 809.79 resp_io: 806.58 resp_cpu: 18721144
Plan cardinality mismatch: best card= 1026.86346210557   curr card= 1026.86346210557
Join order aborted: cost > best plan cost
***********************
***********************
Join order[3]:  PUB_ORGAN_NEW[PO]#1  PUB_STRU_NEW[PS]#0  EMP_BASICINFO[V]#2
***************
Now joining: PUB_STRU_NEW[PS]#0
***************
NL Join
  Outer table: Card: 17714.00  Cost: 60.32  Resp: 60.32  Degree: 1  Bytes: 33
  Inner table: PUB_STRU_NEW  Alias: PS
  Access Path: TableScan
    NL Join:  Cost: 3639494.59  Resp: 3639494.59  Degree: 0
      Cost_io: 3580503.00  Cost_cpu: 343313352694
      Resp_io: 3580503.00  Resp_cpu: 343313352694
kkofmx: index filter:"PS"."IN_USE"='1' AND "PS"."STRU_PATH" LIKE '1#03S000000000000000000000000000#03S990000000000000000000000000%'
kkofmx: index filter:"PS"."STRU_PATH" LIKE '1#03S000000000000000000000000000#03S990000000000000000000000000%'
  Access Path: index (RangeScan)
    Index: STRU_ORGAN_ID_NEW
    resc_io: 5.00  resc_cpu: 56907
    ix_sel: 0.0056225  ix_sel_with_filters: 2.2490e-005
    NL Join: Cost: 88803.54  Resp: 88803.54  Degree: 1
      Cost_io: 88629.00  Cost_cpu: 1015751335
      Resp_io: 88629.00  Resp_cpu: 1015751335
  Best NL cost: 88803.54
          resc: 88803.54 resc_io: 88629.00 resc_cpu: 1015751335
          resp: 88803.54 resp_io: 88629.00 resp_cpu: 1015751335
Join Card:  102.67 = outer (17714.00) * inner (102.67) * sel (5.6453e-005)
Join Card - Rounded: 103 Computed: 102.67
SM Join
  Outer table:
    resc: 60.32  card 17714.00  bytes: 33  deg: 1  resp: 60.32
  Inner table: PUB_STRU_NEW  Alias: PS
    resc: 6.01  card: 102.67  bytes: 564  deg: 1  resp: 6.01
    using dmeth: 2  #groups: 1
    SORT resource      Sort statistics
      Sort width:         231 Area size:      202752 Max Area size:    40684544
      Degree:               1
      Blocks to Sort:     102 Row size:           47 Total Rows:          17714
      Initial runs:         2 Merge passes:        1 IO Cost / pass:         56
      Total IO sort cost: 158      Total CPU sort cost: 19594771
      Total Temp space used: 1582000
    SORT resource      Sort statistics
      Sort width:         231 Area size:      202752 Max Area size:    40684544
      Degree:               1
      Blocks to Sort:       8 Row size:          631 Total Rows:            103
      Initial runs:         1 Merge passes:        0 IO Cost / pass:          0
      Total IO sort cost: 0      Total CPU sort cost: 5850729
      Total Temp space used: 0
  SM join: Resc: 228.71  Resp: 228.71  [multiMatchCost=0.00]
  SM cost: 228.71
     resc: 228.71 resc_io: 223.00 resc_cpu: 33205873
     resp: 228.71 resp_io: 223.00 resp_cpu: 33205873
SM Join (with index on outer)
  Access Path: index (FullScan)
    Index: ORGAN_ID_NEW_KEY
    resc_io: 14539.00  resc_cpu: 112950886
    ix_sel: 1  ix_sel_with_filters: 1
    Cost: 14558.41  Resp: 14558.41  Degree: 1
  Outer table:
    resc: 14558.41  card 17714.00  bytes: 33  deg: 1  resp: 14558.41
  Inner table: PUB_STRU_NEW  Alias: PS
    resc: 6.01  card: 102.67  bytes: 564  deg: 1  resp: 6.01
    using dmeth: 2  #groups: 1
    SORT resource      Sort statistics
      Sort width:         231 Area size:      202752 Max Area size:    40684544
      Degree:               1
      Blocks to Sort:       8 Row size:          631 Total Rows:            103
      Initial runs:         1 Merge passes:        0 IO Cost / pass:          0
      Total IO sort cost: 0      Total CPU sort cost: 5850729
      Total Temp space used: 0
  SM join: Resc: 14565.42  Resp: 14565.42  [multiMatchCost=0.00]
HA Join
  Outer table:
    resc: 60.32  card 17714.00  bytes: 33  deg: 1  resp: 60.32
  Inner table: PUB_STRU_NEW  Alias: PS
    resc: 6.01  card: 102.67  bytes: 564  deg: 1  resp: 6.01
    using dmeth: 2  #groups: 1
    Cost per ptn: 0.96  #ptns: 1
    hash_area: 0 (max=0)   Hash join: Resc: 67.29  Resp: 67.29  [multiMatchCost=0.00]
HA Join (swap)
  Outer table:
    resc: 6.01  card 102.67  bytes: 564  deg: 1  resp: 6.01
  Inner table: PUB_ORGAN_NEW  Alias: PO
    resc: 60.32  card: 17714.00  bytes: 33  deg: 1  resp: 60.32
    using dmeth: 2  #groups: 1
    Cost per ptn: 0.81  #ptns: 1
    hash_area: 0 (max=0)   Hash join: Resc: 67.14  Resp: 67.14  [multiMatchCost=0.00]
  HA cost: 67.14
     resc: 67.14 resc_io: 65.00 resc_cpu: 12457073
     resp: 67.14 resp_io: 65.00 resp_cpu: 12457073
Best:: JoinMethod: Hash
       Cost: 67.14  Degree: 1  Resp: 67.14  Card: 102.67  Bytes: 597
***************
Now joining: EMP_BASICINFO[V]#2
***************
NL Join
  Outer table: Card: 102.67  Cost: 67.14  Resp: 67.14  Degree: 1  Bytes: 597
  Inner table: EMP_BASICINFO  Alias: V
  Access Path: TableScan
    NL Join:  Cost: 131408.03  Resp: 131408.03  Degree: 0
      Cost_io: 129171.00  Cost_cpu: 13018826486
      Resp_io: 129171.00  Resp_cpu: 13018826486
  Access Path: index (AllEqJoinGuess)
    Index: BIN$RvxIUuiYRIKJSSczhjII9w==$0
    resc_io: 9.00  resc_cpu: 74073
    ix_sel: 7.3725e-005  ix_sel_with_filters: 7.3725e-005
    NL Join: Cost: 809.77  Resp: 809.77  Degree: 1
      Cost_io: 806.58  Cost_cpu: 18582544
      Resp_io: 806.58  Resp_cpu: 18582544
  Best NL cost: 809.77
          resc: 809.77 resc_io: 806.58 resc_cpu: 18582544
          resp: 809.77 resp_io: 806.58 resp_cpu: 18582544
Join Card:  1026.86 = outer (102.67) * inner (135666.00) * sel (7.3725e-005)
Join Card - Rounded: 1027 Computed: 1026.86
SM Join
  Outer table:
    resc: 67.14  card 102.67  bytes: 597  deg: 1  resp: 67.14
  Inner table: EMP_BASICINFO  Alias: V
    resc: 1276.70  card: 135666.00  bytes: 60  deg: 1  resp: 1276.70
    using dmeth: 2  #groups: 1
    SORT resource      Sort statistics
      Sort width:         231 Area size:      202752 Max Area size:    40684544
      Degree:               1
      Blocks to Sort:       9 Row size:          667 Total Rows:            103
      Initial runs:         1 Merge passes:        0 IO Cost / pass:          0
      Total IO sort cost: 0      Total CPU sort cost: 5850729
      Total Temp space used: 0
    SORT resource      Sort statistics
      Sort width:         231 Area size:      202752 Max Area size:    40684544
      Degree:               1
      Blocks to Sort:    1263 Row size:           76 Total Rows:         135666
      Initial runs:         2 Merge passes:        1 IO Cost / pass:        686
      Total IO sort cost: 1949      Total CPU sort cost: 141136459
      Total Temp space used: 18539000
  SM join: Resc: 3318.10  Resp: 3318.10  [multiMatchCost=0.00]
  SM cost: 3318.10
     resc: 3318.10 resc_io: 3269.00 resc_cpu: 285719692
     resp: 3318.10 resp_io: 3269.00 resp_cpu: 285719692
HA Join
  Outer table:
    resc: 67.14  card 102.67  bytes: 597  deg: 1  resp: 67.14
  Inner table: EMP_BASICINFO  Alias: V
    resc: 1276.70  card: 135666.00  bytes: 60  deg: 1  resp: 1276.70
    using dmeth: 2  #groups: 1
    Cost per ptn: 2.83  #ptns: 1
    hash_area: 0 (max=0)   Hash join: Resc: 1346.67  Resp: 1346.67  [multiMatchCost=0.00]
  HA cost: 1346.67
     resc: 1346.67 resc_io: 1320.00 resc_cpu: 155224404
     resp: 1346.67 resp_io: 1320.00 resp_cpu: 155224404
Join order aborted: cost > best plan cost
***********************
***********************
Join order[4]:  PUB_ORGAN_NEW[PO]#1  EMP_BASICINFO[V]#2  PUB_STRU_NEW[PS]#0
***************
Now joining: EMP_BASICINFO[V]#2
***************
NL Join
  Outer table: Card: 17714.00  Cost: 60.32  Resp: 60.32  Degree: 1  Bytes: 33
  Inner table: EMP_BASICINFO  Alias: V
  Access Path: TableScan
    NL Join:  Cost: 22587811.45  Resp: 22587811.45  Degree: 0
      Cost_io: 22203453.00  Cost_cpu: 2236850685471
      Resp_io: 22203453.00  Resp_cpu: 2236850685471
  Best NL cost: 22587811.45
          resc: 22587811.45 resc_io: 22203453.00 resc_cpu: 2236850685471
          resp: 22587811.45 resp_io: 22203453.00 resp_cpu: 2236850685471
Join Card:  2403187524.00 = outer (17714.00) * inner (135666.00) * sel (1)
Join Card - Rounded: 2403187524 Computed: 2403187524.00
Join order aborted: cost > best plan cost
***********************
***********************
Join order[5]:  EMP_BASICINFO[V]#2  PUB_STRU_NEW[PS]#0  PUB_ORGAN_NEW[PO]#1
Join order aborted: cost > best plan cost
***********************
***********************
Join order[6]:  EMP_BASICINFO[V]#2  PUB_ORGAN_NEW[PO]#1  PUB_STRU_NEW[PS]#0
Join order aborted: cost > best plan cost
***********************
(newjo-stop-1) k:0, spcnt:0, perm:6, maxperm:2000
*********************************
Number of join permutations tried: 6
*********************************
(newjo-save)    [1 0 2 ]
Final - All Rows Plan:  Best join order: 1         
  Cost: 809.7705  Degree: 1  Card: 1027.0000  Bytes: 674739
  Resc: 809.7705  Resc_io: 806.5774  Resc_cpu: 18582544
  Resp: 809.7705  Resp_io: 806.5774  Resc_cpu: 18582544
kkoipt: Query block SEL$1 (#0)
******* UNPARSED QUERY IS *******
SELECT "V"."EMP_ID" "EMP_ID","PS"."ORGAN_ALIAS" "ORGAN_ALIAS","PO"."ORGAN_ID" "ORGAN_ID","PO"."IN_USE" "IN_USE" FROM "HEBEI"."EMP_BASICINFO" "V","HEBEI"."PUB_STRU_NEW" "PS","HEBEI"."PUB_ORGAN_NEW" "PO" WHERE "V"."ORGAN_ID"="PS"."ORGAN_ID" AND "PS"."STRU_PATH" LIKE '1#03S000000000000000000000000000#03S990000000000000000000000000%' AND "PS"."IN_USE"='1' AND "PS"."ORGAN_ID"="PO"."ORGAN_ID"
kkoqbc-end
          : call(in-use=71244, alloc=0), compile(in-use=41064, alloc=0)
apadrv-end: call(in-use=71244, alloc=0), compile(in-use=41752, alloc=0)

sql_id=915c9rw5ucvg0.
Current SQL statement for this session:
explain plan for select v.emp_id,ps.organ_alias,po.organ_id,po.in_use
  from emp_basicinfo v,pub_stru_new ps,pub_organ_new po
where  ps.organ_id = po.organ_id
   and v.organ_id = ps.organ_id
   and ps.in_use= '1'
   and ps.stru_path like '1#03S000000000000000000000000000#03S990000000000000000000000000%'
   

============
Plan Table
============
------------------------------------------------------------------------+-----------------------------------+
| Id  | Operation                       | Name                          | Rows  | Bytes | Cost  | Time      |
------------------------------------------------------------------------+-----------------------------------+
| 0   | SELECT STATEMENT                |                               |       |       |   810 |           |
| 1   |  TABLE ACCESS BY INDEX ROWID    | EMP_BASICINFO                 |    10 |   600 |     9 |  00:00:01 |
| 2   |   NESTED LOOPS                  |                               |  1027 |  659K |   810 |  00:00:10 |
| 3   |    HASH JOIN                    |                               |   103 |   60K |    67 |  00:00:01 |
| 4   |     TABLE ACCESS BY INDEX ROWID | PUB_STRU_NEW                  |   103 |   57K |     6 |  00:00:01 |
| 5   |      INDEX RANGE SCAN           | STRU_ORGAN_ID_NEW             |     1 |       |     5 |  00:00:01 |
| 6   |     TABLE ACCESS FULL           | PUB_ORGAN_NEW                 |   17K |  571K |    60 |  00:00:01 |
| 7   |    INDEX RANGE SCAN             | BIN$RvxIUuiYRIKJSSczhjII9w==$0|    10 |       |     2 |  00:00:01 |
------------------------------------------------------------------------+-----------------------------------+
Predicate Information:
----------------------
3 - access("PS"."ORGAN_ID"="PO"."ORGAN_ID")
5 - access("PS"."STRU_PATH" LIKE '1#03S000000000000000000000000000#03S990000000000000000000000000%' AND "PS"."IN_USE"='1')
5 - filter(("PS"."STRU_PATH" LIKE '1#03S000000000000000000000000000#03S990000000000000000000000000%' AND "PS"."IN_USE"='1'))
7 - access("V"."ORGAN_ID"="PS"."ORGAN_ID")

Content of other_xml column
===========================

xclwps

又发现：CBO在分析JOIN 代价的时候。
只有 NL 会重新分析扫描方式；
HA join  和 SM join 都不会再次分析表的扫描方式，而是直接使用前面 SINGLE TABLE ACCESS PATH 过程中分析出的最优访问路径 数据。

Yong Huang

Thanks for sharing.

> 首先：CBO优化器会计算 SINGLE TABLE ACCESS PATH  中使用各种扫描的情况，但是最终使用的索引并不完全由这个决定。

You mean, "sql_A那样根本就没得选" is not right, its "Best:: AccessPath: IndexFFS  Index: ORGAN_ID_NEW_KEY" is not the overall best access path?

> 又发现：CBO在分析JOIN 代价的时候。
> 只有 NL 会重新分析扫描方式；

Can you clarify "重新分析扫描方式"?

xclwps

我随便摘取上面10053 中连续的一段：
在 SINGLE TABLE ACCESS PATH 的时候只分析了 TableScan 这一种方式；
但是在分析NL Join 代价的时候又从新分析了一遍；而Ha Join 和 SM Join 却没有Access Path 的分析,应该是直接使用了 SINGLE TABLE ACCESS PATH 中的Best Access Path 来进一步计算其综合cost.

***************************************
         
Table: PUB_ORGAN_NEW  Alias: PO     
    Card: Original: 17714  Rounded: 17714  Computed: 17714.00  Non Adjusted: 17714.00
  Access Path: TableScan
    Cost:  60.32  Resp: 60.32  Degree: 0
      Cost_io: 59.00  Cost_cpu: 7697194
      Resp_io: 59.00  Resp_cpu: 7697194
  Best:: AccessPath: TableScan
         Cost: 60.32  Degree: 1  Resp: 60.32  Card: 17714.00  Bytes: 0
***************************************
NL Join
  Outer table: Card: 102.67  Cost: 6.01  Resp: 6.01  Degree: 1  Bytes: 564
  Inner table: PUB_ORGAN_NEW  Alias: PO
  Access Path: TableScan
    NL Join:  Cost: 6002.24  Resp: 6002.24  Degree: 0
      Cost_io: 5866.00  Cost_cpu: 792874202
      Resp_io: 5866.00  Resp_cpu: 792874202
  Access Path: index (UniqueScan)
    Index: ORGAN_ID_NEW_KEY
    resc_io: 1.00  resc_cpu: 9351
    ix_sel: 5.6453e-005  ix_sel_with_filters: 5.6453e-005
    NL Join: Cost: 106.09  Resp: 106.09  Degree: 1
      Cost_io: 105.92  Cost_cpu: 1003428
      Resp_io: 105.92  Resp_cpu: 1003428
  Access Path: index (AllEqUnique)
    Index: ORGAN_ID_NEW_KEY
    resc_io: 1.00  resc_cpu: 9351
    ix_sel: 5.6453e-005  ix_sel_with_filters: 5.6453e-005
    NL Join: Cost: 106.09  Resp: 106.09  Degree: 1
      Cost_io: 105.92  Cost_cpu: 1003428
      Resp_io: 105.92  Resp_cpu: 1003428
  Best NL cost: 106.09
          resc: 106.09 resc_io: 105.92 resc_cpu: 1003428
          resp: 106.09 resp_io: 105.92 resp_cpu: 1003428
Join Card:  102.67 = outer (102.67) * inner (17714.00) * sel (5.6453e-005)

SM Join   这个就没有再分析Access Path
Outer table:
    resc: 6.01  card 102.67  bytes: 564  deg: 1  resp: 6.01
  Inner table: PUB_ORGAN_NEW  Alias: PO
    resc: 60.32  card: 17714.00  bytes: 33  deg: 1  resp: 60.32
    using dmeth: 2  #groups: 1
    SORT resource      Sort statistics
      Sort width:         231 Area size:      202752 Max Area size:    40684544
      Degree:               1
      Blocks to Sort:       8 Row size:          631 Total Rows:            103
      Initial runs:         1 Merge passes:        0 IO Cost / pass:          0
      Total IO sort cost: 0      Total CPU sort cost: 5850729
      Total Temp space used: 0
    SORT resource      Sort statistics
      Sort width:         231 Area size:      202752 Max Area size:    40684544
      Degree:               1
      Blocks to Sort:     102 Row size:           47 Total Rows:          17714
      Initial runs:         2 Merge passes:        1 IO Cost / pass:         56
      Total IO sort cost: 158      Total CPU sort cost: 19594771
      Total Temp space used: 1582000
  SM join: Resc: 228.71  Resp: 228.71  [multiMatchCost=0.00]
  SM cost: 228.71
     resc: 228.71 resc_io: 223.00 resc_cpu: 33205873
     resp: 228.71 resp_io: 223.00 resp_cpu: 33205873

HA Join   这个也没有再次分析 Access Path  
Outer table:
    resc: 6.01  card 102.67  bytes: 564  deg: 1  resp: 6.01
  Inner table: PUB_ORGAN_NEW  Alias: PO
    resc: 60.32  card: 17714.00  bytes: 33  deg: 1  resp: 60.32
    using dmeth: 2  #groups: 1
    Cost per ptn: 0.81  #ptns: 1
    hash_area: 0 (max=0)   Hash join: Resc: 67.14  Resp: 67.14  [multiMatchCost=0.00]
  HA cost: 67.14
     resc: 67.14 resc_io: 65.00 resc_cpu: 12457073
     resp: 67.14 resp_io: 65.00 resp_cpu: 12457073

因此CBO得出 HA Join 的比NL Join 和SM Join 的Cost 都小，所以使用了HA Join。

最终执行计划：
============
Plan Table
============
---------------------------------------------------------+-----------------------------------+
| Id  | Operation                     | Name             | Rows  | Bytes | Cost  | Time      |
---------------------------------------------------------+-----------------------------------+
| 0   | SELECT STATEMENT              |                  |       |       |    67 |           |
| 1   |  HASH JOIN                    |                  |   103 |   61K |    67 |  00:00:01 |
| 2   |   TABLE ACCESS BY INDEX ROWID | PUB_STRU_NEW     |   103 |   57K |     6 |  00:00:01 |
| 3   |    INDEX RANGE SCAN           | STRU_ORGAN_ID_NEW|     1 |       |     5 |  00:00:01 |
| 4   |   TABLE ACCESS FULL           | PUB_ORGAN_NEW    |   17K |  761K |    60 |  00:00:01 |
---------------------------------------------------------+-----------------------------------+
Predicate Information:
----------------------
1 - access("PS"."ORGAN_ID"="PO"."ORGAN_ID")
3 - access("PS"."STRU_PATH" LIKE '1#03S000000000000000000000000000#03S990000000000000000000000000%' AND "PS"."IN_USE"='1')
3 - filter(("PS"."STRU_PATH" LIKE '1#03S000000000000000000000000000#03S990000000000000000000000000%' AND "PS"."IN_USE"='1'))

xclwps

其实我后面的贴应该已经自己解决了“sql_A那样根本就没得选”这个疑问。

anlinew

搞清楚NL、HJ、SM分别怎么做，自然而然就知道了。。。。

sundog315

xclwps 发表于 2012-6-29 01:18
******************************
  optimizer_mode_hinted               = false
  optimizer_featu ...

设置了这么多关于优化器的隐含参数，你确定这些参数都是合理的么？

xclwps

sundog315 发表于 2012-7-2 10:43
设置了这么多关于优化器的隐含参数，你确定这些参数都是合理的么？

测试机，参数都是默认的！
数据量不大，都是1万多条，

Yong Huang

> SM Join   这个就没有再分析Access Path
> HA Join   这个也没有再次分析 Access Path

As far as I know, "Access Path" in the 10053 trace file has a specific meaning. It's only applicable to nested loop join (as well as single table scan), indicating the driving and driven tables. SM and hash joins work differently and have no driving and driven tables. (Hash join has build and probe tables). So, saying "最终的执行计划基本是根据 在具体连接中得出的最优AccessPath 来决定的" is not quite right, because the term "Access Path" is only used by Oracle for NL join. Just say "最优 path".

> SINGLE TABLE ACCESS PATH         这个其实并不能最终决定扫描方式，因为在表 链接cost 计算的时候还会重新计算。
>  Table: PUB_ORGAN_NEW  Alias: PO

Where do you see "SINGLE TABLE ACCESS PATH" for table PO again? I only see it once.

General comment: I suggest you collect stats on all tables involved, including PUB_STRU_NEW, purge recyclebin, and trace again. Read Jonathan Lewis's "Cost Based Oracle" p.416 (beginning with "General Plans") to the end of Chapter 14.

anlinew

sort merge join和HASH join都是两表分别access后的结果集进行join，因此对于这两个表来说其分别都是single table access
Nest loop join会利用driving表singgle table access后的结果集来循环join driven table，因此需要driven table在join字段上有索引，并且利用改索引来访问table，而这个执行路径是在single table access方式下没有的