如何让oracle自动使用索引

yinghao

优化器不认为走索引成本更小，
把表的数据按照索引的字段排序后重新插入.
例如:
create table table1_bak as select  * from table1 order by OrgCode,PluCode;
truncate table table 1;
insert into table1
select * from table1_bak
order by OrgCode,PluCode;
commit;

QDZHT

开始也怀疑是数据分布造成的，查了一下索引的聚合因子，的确很大，像搂主所说的一样按索引重新导入过数据，但对成本的计算没有影响。

用10053事件跟踪了一下
发现NL成本的计算和外表的记录数有关，当外表记录数多时，NL的成本比较高，此时会用HJ，不用索引；当外表记录数少时，NL的成本低于HJ，此时就使用了索引。
NL的成本=Outer table cost  + Outer Table cdn(表记录)*Inner table cost

下面是10053的跟踪结果
SELECT *
  FROM TDSTDPSDATA_TEmp A
WHERE EXISTS (SELECT *
          FROM TSTKPC_TEmp
         WHERE ORGCODE = A.SHORGCODE
           AND PLUID = A.PLUID
           AND JSCODe = '0')

***************************************
PARAMETERS USED BY THE OPTIMIZER
********************************  
OPTIMIZER_FEATURES_ENABLE = 9.2.0
OPTIMIZER_MODE/GOAL = Choose
_OPTIMIZER_PERCENT_PARALLEL = 101
HASH_AREA_SIZE = 1048576
HASH_JOIN_ENABLED = TRUE
HASH_MULTIBLOCK_IO_COUNT = 0
SORT_AREA_SIZE = 524288
OPTIMIZER_SEARCH_LIMIT = 5
PARTITION_VIEW_ENABLED = FALSE
_ALWAYS_STAR_TRANSFORMATION = FALSE
_B_TREE_BITMAP_PLANS = TRUE
STAR_TRANSFORMATION_ENABLED = FALSE
_COMPLEX_VIEW_MERGING = TRUE
_PUSH_JOIN_PREDICATE = TRUE
PARALLEL_BROADCAST_ENABLED = TRUE
OPTIMIZER_MAX_PERMUTATIONS = 2000
OPTIMIZER_INDEX_CACHING = 0
_SYSTEM_INDEX_CACHING = 0
OPTIMIZER_INDEX_COST_ADJ = 100
OPTIMIZER_DYNAMIC_SAMPLING = 1
_OPTIMIZER_DYN_SMP_BLKS = 32
QUERY_REWRITE_ENABLED = FALSE
QUERY_REWRITE_INTEGRITY = ENFORCED
_INDEX_JOIN_ENABLED = TRUE
_SORT_ELIMINATION_COST_RATIO = 0
_OR_EXPAND_NVL_PREDICATE = TRUE
_NEW_INITIAL_JOIN_ORDERS = TRUE
ALWAYS_ANTI_JOIN = CHOOSE
ALWAYS_SEMI_JOIN = CHOOSE
_OPTIMIZER_MODE_FORCE = TRUE
_OPTIMIZER_UNDO_CHANGES = FALSE
_UNNEST_SUBQUERY = TRUE
_PUSH_JOIN_UNION_VIEW = TRUE
_FAST_FULL_SCAN_ENABLED = TRUE
_OPTIM_ENHANCE_NNULL_DETECTION = TRUE
_ORDERED_NESTED_LOOP = TRUE
_NESTED_LOOP_FUDGE = 100
_NO_OR_EXPANSION = FALSE
_QUERY_COST_REWRITE = TRUE
QUERY_REWRITE_EXPRESSION = TRUE
_IMPROVED_ROW_LENGTH_ENABLED = TRUE
_USE_NOSEGMENT_INDEXES = FALSE
_ENABLE_TYPE_DEP_SELECTIVITY = TRUE
_IMPROVED_OUTERJOIN_CARD = TRUE
_OPTIMIZER_ADJUST_FOR_NULLS = TRUE
_OPTIMIZER_CHOOSE_PERMUTATION = 0
_USE_COLUMN_STATS_FOR_FUNCTION = TRUE
_SUBQUERY_PRUNING_ENABLED = TRUE
_SUBQUERY_PRUNING_REDUCTION_FACTOR = 50
_SUBQUERY_PRUNING_COST_FACTOR = 20
_LIKE_WITH_BIND_AS_EQUALITY = FALSE
_TABLE_SCAN_COST_PLUS_ONE = TRUE
_SORTMERGE_INEQUALITY_JOIN_OFF = FALSE
_DEFAULT_NON_EQUALITY_SEL_CHECK = TRUE
_ONESIDE_COLSTAT_FOR_EQUIJOINS = TRUE
_OPTIMIZER_COST_MODEL = CHOOSE
_GSETS_ALWAYS_USE_TEMPTABLES = FALSE
DB_FILE_MULTIBLOCK_READ_COUNT = 16
_NEW_SORT_COST_ESTIMATE = TRUE
_GS_ANTI_SEMI_JOIN_ALLOWED = TRUE
_CPU_TO_IO = 0
_PRED_MOVE_AROUND = TRUE

***************************************
BASE STATISTICAL INFORMATION
***********************
Table stats    Table: TDSTDPSDATA_TEMP   Alias:  A
  TOTAL ::  CDN: 33031  NBLKS:  810  AVG_ROW_LEN:  170
Column:  SHORGCODE  Col#: 9      Table: TDSTDPSDATA_TEMP   Alias:  A
    NDV: 56        NULLS: 0         DENS: 1.7857e-002
    NO HISTOGRAM: #BKT: 1 #VAL: 2
Column:      PLUID  Col#: 14     Table: TDSTDPSDATA_TEMP   Alias:  A
    NDV: 8914      NULLS: 0         DENS: 1.1218e-004
    NO HISTOGRAM: #BKT: 1 #VAL: 2
Column:      PLUID  Col#: 14     Table: TDSTDPSDATA_TEMP   Alias:  A
    NDV: 8914      NULLS: 0         DENS: 1.1218e-004
    NO HISTOGRAM: #BKT: 1 #VAL: 2
Column:  SHORGCODE  Col#: 9      Table: TDSTDPSDATA_TEMP   Alias:  A
    NDV: 56        NULLS: 0         DENS: 1.7857e-002
    NO HISTOGRAM: #BKT: 1 #VAL: 2
-- Index stats
  INDEX NAME: IDX_TDSTDPSDATA_ORGPLU_TEMP  COL#: 4 14
    TOTAL ::  LVLS: 1   #LB: 93  #DK: 8914  LB/K: 1  DB/K: 1  CLUF: 924
  INDEX NAME: IDX_TDSTDPSDATA_PSJOBNO_TEMP  COL#: 25
    TOTAL ::  LVLS: 0   #LB: 1  #DK: 34  LB/K: 1  DB/K: 1  CLUF: 66
  INDEX NAME: PK_TDSTDPSDATA_TEMP  COL#: 2 3 6
    TOTAL ::  LVLS: 2   #LB: 270  #DK: 33031  LB/K: 1  DB/K: 1  CLUF: 20246
***********************
Table stats    Table: TSTKPC_TEMP   Alias: TSTKPC_TEMP
  TOTAL ::  CDN: 1821668  NBLKS:  27181  AVG_ROW_LEN:  105
Column:    ORGCODE  Col#: 1      Table: TSTKPC_TEMP   Alias: TSTKPC_TEMP
    NDV: 52        NULLS: 0         DENS: 1.9231e-002
    NO HISTOGRAM: #BKT: 1 #VAL: 2
Column:      PLUID  Col#: 2      Table: TSTKPC_TEMP   Alias: TSTKPC_TEMP
    NDV: 50666     NULLS: 0         DENS: 1.9737e-005
    NO HISTOGRAM: #BKT: 1 #VAL: 2
-- Index stats
  INDEX NAME: IDX_TSTKPC_ETPCODE_TEMP  COL#: 7
    TOTAL ::  LVLS: 2   #LB: 6177  #DK: 1198  LB/K: 5  DB/K: 38  CLUF: 46043
  INDEX NAME: IDX_TSTKPC_TEMP  COL#: 1 2 3 4
    TOTAL ::  LVLS: 2   #LB: 10072  #DK: 1782067  LB/K: 1  DB/K: 1  CLUF: 27251
  INDEX NAME: PK_TSTKPC_TEMP  COL#: 5
    TOTAL ::  LVLS: 2   #LB: 9846  #DK: 1821668  LB/K: 1  DB/K: 1  CLUF: 1598377
_OPTIMIZER_PERCENT_PARALLEL = 0
***************************************
SINGLE TABLE ACCESS PATH
Column:     JSCODE  Col#: 9      Table: TSTKPC_TEMP   Alias: TSTKPC_TEMP
    NDV: 3         NULLS: 0         DENS: 3.3333e-001
    NO HISTOGRAM: #BKT: 1 #VAL: 2
  TABLE: TSTKPC_TEMP     ORIG CDN: 1821668  ROUNDED CDN: 607223  CMPTD CDN: 607223
  Access path: tsc  Resc:  2616  Resp:  2616
  BEST_CST: 2616.00  PATH: 2  Degree:  1
***************************************
SINGLE TABLE ACCESS PATH
Column:    ORGCODE  Col#: 4      Table: TDSTDPSDATA_TEMP   Alias:  A
    NDV: 2         NULLS: 0         DENS: 5.0000e-001
    NO HISTOGRAM: #BKT: 1 #VAL: 2
  TABLE: TDSTDPSDATA_TEMP     ORIG CDN: 33031  ROUNDED CDN: 16516  CMPTD CDN: 16516
  Access path: tsc  Resc:  79  Resp:  79
  Skip scan: ss-sel 1  andv 4457  
    ss cost 4457
    index io scan cost 0
  Access path: index (scan)
      Index: IDX_TDSTDPSDATA_ORGPLU_TEMP
  TABLE: TDSTDPSDATA_TEMP
      RSC_CPU: 0   RSC_IO: 510
  IX_SEL:  5.0000e-001  TB_SEL:  5.0000e-001
  BEST_CST: 79.00  PATH: 2  Degree:  1
***************************************
OPTIMIZER STATISTICS AND COMPUTATIONS
***************************************
GENERAL PLANS
***********************
Join order[1]: TDSTDPSDATA_TEMP [ A] TSTKPC_TEMP [TSTKPC_TEMP]
Now joining: TSTKPC_TEMP [TSTKPC_TEMP] *******
NL Join
Outer table: cost: 79  cdn: 16516  rcz: 150  resp:  79
  Inner table: TSTKPC_TEMP
    Access path: tsc  Resc: 2616
    Join:  Resc:  43205935  Resp:  43205935
  Access path: index (scan)
      Index: IDX_TSTKPC_TEMP
  TABLE: TSTKPC_TEMP
      RSC_CPU: 0   RSC_IO: 3
  IX_SEL:  3.7956e-007  TB_SEL:  3.7956e-007
    Join:  resc: 49627  resp: 49627
Semi-join cardinality:  15336 = outer (16516) * sel (9.2857e-001) [flag=12]
  Best NL cost: 49627  resp: 49627
SM Join
  Outer table:
    resc: 79  cdn: 16516  rcz: 150  deg: 1  resp: 79
  Inner table: TSTKPC_TEMP
    resc: 2616  cdn: 607223  rcz: 13  deg:  1  resp: 2616
    using join:1 distribution:2 #groups:1
    SORT resource      Sort statistics
      Sort width:            6 Area size:      131072 Max Area size:     2621440   Degree: 1
      Blocks to Sort:      354 Row size:          175 Rows:      16516
      Initial runs:          2 Merge passes:        1 IO Cost / pass:        413
      Total IO sort cost: 384
      Total CPU sort cost: 0
      Total Temp space used: 7529000
    SORT resource      Sort statistics
      Sort width:            6 Area size:      131072 Max Area size:     2621440   Degree: 1
      Blocks to Sort:     1859 Row size:           25 Rows:     607223
      Initial runs:          6 Merge passes:        1 IO Cost / pass:       2169
      Total IO sort cost: 2014
      Total CPU sort cost: 0
      Total Temp space used: 29271000
  Merge join  Cost:  5093  Resp:  5093
HA Join
  Outer table:
    resc: 79  cdn: 16516  rcz: 150  deg: 1  resp: 79
  Inner table: TSTKPC_TEMP
    resc: 2616  cdn: 607223  rcz: 13  deg:  1  resp: 2616
    using join:8 distribution:2 #groups:1
  Hash join one ptn Resc: 438   Deg: 1
      hash_area:  60 (max=640)  buildfrag:  327                probefrag:   1854 ppasses:    1
  Hash join   Resc: 3133   Resp: 3133
Join result: cost: 3133  cdn: 15336  rcz: 163
Best so far: TABLE#: 0  CST:         79  CDN:      16516  BYTES:    2477400
Best so far: TABLE#: 1  CST:       3133  CDN:      15336  BYTES:    2499768
***********************
Join order[2]: TSTKPC_TEMP [TSTKPC_TEMP] TDSTDPSDATA_TEMP [ A]
    SORT resource      Sort statistics
      Sort width:            6 Area size:      131072 Max Area size:     2621440   Degree: 1
      Blocks to Sort:     1859 Row size:           25 Rows:     607223
      Initial runs:          6 Merge passes:        1 IO Cost / pass:       2169
      Total IO sort cost: 2014
      Total CPU sort cost: 0
      Total Temp space used: 29271000
Final:
  CST: 3133  CDN: 15336  RSC: 3133  RSP: 3133  BYTES: 2499768
  IO-RSC: 3133  IO-RSP: 3133  CPU-RSC: 0  CPU-RSP: 0


以下是Oracle官方的解释：
The database is CBO based which means Oracle will choose the execution plan with less cost.
In oracle 9i,HJ has lower cost comparing to NL (normally),so oracle will always choose HJ instead of NL.
If you really need less logical read in the query,you should force the database choose NL instead of HJ.

感觉HJ的算法有问题，虽然Oracle认为NL的成本比HJ高，但实际上NL的速度的确比用HJ要快数十倍。

有没有遇到同种情况的朋友，请指教。

anlinew

[php]Table stats Table: TDSTDPSDATA_TEMP Alias: A
TOTAL :: CDN: 33031 NBLKS: 810 AVG_ROW_LEN: 170

Table stats Table: TSTKPC_TEMP Alias: TSTKPC_TEMP
TOTAL :: CDN: 1821668 NBLKS: 27181 AVG_ROW_LEN: 105

----
[/php]
哪个是a 表，哪个是b表

anlinew

这个trace 的sql跟首贴的sql都不一样
[php]
SELECT *
FROM TDSTDPSDATA_TEmp A
WHERE EXISTS (SELECT *
FROM TSTKPC_TEmp
WHERE ORGCODE = A.SHORGCODE
AND PLUID = A.PLUID
AND JSCODe = '0')

=
select A.*
From TDSTDPSDATA_TEmp A,TSTKPC_TEmp B
WHERE B.ORGCODE = A.SHORGCODE
AND B.PLUID = A.PLUID
AND B.JSCODe = '0'

vs

select A.*
From TDSTDPSDATA_TEmp A,TSTKPC_TEmp B
where A.OrgCode=B.OrgCode
and A.PluCode=B.PluCode
and A.Status='1'
－－－
[/php]

zhuojm

现象是不是每次都能重现?

flying.hg

感覺和hwm有關！

msrennan

最初由 QDZHT 发布
[B]用10053事件跟踪了一下[/B]

是什么意思啊?能否给二三句话讲一下.

Yong Huang

最初由 QDZHT 发布
[B]感觉HJ的算法有问题，虽然Oracle认为NL的成本比HJ高，但实际上NL的速度的确比用HJ要快数十倍。
[/B]

If your Tar is still open with Oracle, ask them to give an explanation. What you quoted is nothing.

If you run through SQL trace, do the trace files show plans consistent with the 10053 trace? In the case of hash join, after you run the query, can you find out if it indeed uses one-pass hash join? You can check v$sql_workarea or perhaps check the value of 'workarea executions - onepass' in v$sesstat. Or simply watch v$tempstat for I/O. It should show no increment.

If you suspect hash join calculation underestimates the cost, Oracle needs to explain how the one partition cost 438 is calculated. Jonathan Lewis's CBO book p.381 has a sample calculation. I need your db_block_size to apply his formula.

Yong Huang

QDZHT

Thanks!