oracle不足某个数取这个数,取值不同，为什么低于某个值就不走索引

本帖最后由 hawker60 于 2012-11-19 13:32 编辑

再贴一个10053的trace*** SESSION ID

544.34252) 2012-11-19 12:51:25.756

***************************************

PARAMETERS USED BY THE OPTIMIZER

********************************

OPTIMIZER_FEATURES_ENABLE = 9.2.0

OPTIMIZER_MODE/GOAL = Choose

_OPTIMIZER_PERCENT_PARALLEL = 101

HASH_AREA_SIZE = 1200

HASH_JOIN_ENABLED = TRUE

HASH_MULTIBLOCK_IO_COUNT = 0

SORT_AREA_SIZE = 600

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: A   Alias:  A

TOTAL ::  CDN: 6114140  NBLKS:  174570  AVG_ROW_LEN:  217

-- Index stats

INDEX NAME: IDX_ORDEREDPRODUCT_1  COL#: 5

TOTAL ::  LVLS: 2   #LB: 31820  #DK: 1  LB/K: 31820  DB/K: 196340  CLUF: 196340

INDEX NAME: IDX_ORDEREDPRODUCT_2  COL#: 17

TOTAL ::  LVLS: 2   #LB: 32360  #DK: 1  LB/K: 32360  DB/K: 201100  CLUF: 201100

INDEX NAME: IDX_ORDEREDPRODUCT_3  COL#: 20

TOTAL ::  LVLS: 2   #LB: 32000  #DK: 5715560  LB/K: 1  DB/K: 1  CLUF: 5578100

INDEX NAME: IDX_ORDEREDPRODUCT_5  COL#: 19

TOTAL ::  LVLS: 2   #LB: 33080  #DK: 348  LB/K: 95  DB/K: 8201  CLUF: 2854000

INDEX NAME: ITOAFORDERPRODORDERID  COL#: 1

TOTAL ::  LVLS: 2   #LB: 26280  #DK: 5698520  LB/K: 1  DB/K: 1  CLUF: 5337280

INDEX NAME: I_ITEMID  COL#: 2

TOTAL ::  LVLS: 2   #LB: 30860  #DK: 14  LB/K: 2204  DB/K: 22932  CLUF: 321060

INDEX NAME: I_OAFDELIVERYADDRESSID  COL#: 7

TOTAL ::  LVLS: 2   #LB: 35020  #DK: 129931  LB/K: 1  DB/K: 42  CLUF: 5501620

INDEX NAME: I_ORDEREDPRODPRODID  COL#: 4

TOTAL ::  LVLS: 2   #LB: 39100  #DK: 1915  LB/K: 20  DB/K: 1522  CLUF: 2915820

INDEX NAME: I_ORDERPRODCUSTID  COL#: 3

TOTAL ::  LVLS: 2   #LB: 35940  #DK: 109851  LB/K: 1  DB/K: 52  CLUF: 5792340

INDEX NAME: I_ORDERPRODMULTICODEID  COL#: 6

TOTAL ::  LVLS: 2   #LB: 21890  #DK: 2713  LB/K: 8  DB/K: 1179  CLUF: 3200777

INDEX NAME: I_ORDPRDORDSTATUS  COL#: 16

TOTAL ::  LVLS: 5   #LB: 9946  #DK: 7  LB/K: 1420  DB/K: 276781  CLUF: 1937468

INDEX NAME: I_PAYMENTSTATUS  COL#: 15

TOTAL ::  LVLS: 2   #LB: 26840  #DK: 3  LB/K: 8946  DB/K: 187580  CLUF: 562740

INDEX NAME: I_STATUS_VERIFICATIONSTATUS  COL#: 16 39

TOTAL ::  LVLS: 2   #LB: 23260  #DK: 9  LB/K: 2584  DB/K: 78213  CLUF: 703920

INDEX NAME: I_VERIFICATIONSTATUS  COL#: 39

TOTAL ::  LVLS: 6   #LB: 4187  #DK: 2  LB/K: 2093  DB/K: 451273  CLUF: 902547

INDEX NAME: ORDEREDPRODUCT_PK  COL#: 1 2

TOTAL ::  LVLS: 2   #LB: 30100  #DK: 6084400  LB/K: 1  DB/K: 1  CLUF: 5447000

_OPTIMIZER_PERCENT_PARALLEL = 0

***************************************

SINGLE TABLE ACCESS PATH

Column: CREATEDTIM  Col#: 20     Table: A   Alias:  A

NDV: 4765161   NULLS: 0         DENS: 2.0986e-07 LO:  2454072  HI: 2456251

NO HISTOGRAM: #BKT: 1 #VAL: 2

TABLE: A     ORIG CDN: 6114140  ROUNDED CDN: 23632  CMPTD CDN: 23632

Access path: tsc  Resc:  16791  Resp:  16791

Access path: index (scan)

Index: IDX_ORDEREDPRODUCT_3

TABLE: A

RSC_CPU: 0   RSC_IO: 21687

IX_SEL:  3.8652e-03  TB_SEL:  3.8652e-03

Access path: index (index-only)

Index: IDX_ORDEREDPRODUCT_3

TABLE: A

RSC_CPU: 0   RSC_IO: 126

IX_SEL:  3.8652e-03  TB_SEL:  3.8652e-03

SORT resource      Sort statistics

Sort width:          229 Area size:     1048576 Max Area size:    80530432   Degree: 1

Blocks to Sort:       61 Row size:           21 Rows:      23633

Initial runs:          1 Merge passes:        1 IO Cost / pass:         84

Total IO sort cost: 72

Total CPU sort cost: 0

Total Temp space used: 0

Access path: index (no sta/stp keys)

Index: ITOAFORDERPRODORDERID

TABLE: A

RSC_CPU: 0   RSC_IO: 26282

IX_SEL:  1.0000e+00  TB_SEL:  1.0000e+00

******** Cost index join ********

Ix HA Join

Outer table:

resc: 158  cdn: 23632  rcz: 18  deg: 1  resp: 158

Inner table:

resc: 32852  cdn: 6114140  rcz: 16  deg:  1  resp: 32852

using join:8 distribution:2 #groups:1

Hash join one ptn Resc: 890   Deg: 1

hash_area:  256 (max=19661)  buildfrag:  87                probefrag:   20898 ppasses:    1

Hash join   Resc: 33900   Resp: 33900

******** Index join cost ********

Cost: 33900

BEST_CST: 16791.00  PATH: 2  Degree:  1

***************************************

OPTIMIZER STATISTICS AND COMPUTATIONS

***************************************

GENERAL PLANS

***********************

Join order[1]:  A[A]#0

Best so far: TABLE#: 0  CST:      16791  CDN:      23632  BYTES:     330848

Final - All Rows Plan:

JOIN ORDER: 1

CST: 16791  CDN: 23632  RSC: 16791  RSP: 16791  BYTES: 330848

IO-RSC: 16791  IO-RSP: 16791  CPU-RSC: 0  CPU-RSP: 0