There is a table T.
num_rows: 39313156
blocks: 1368177
The table has columns f1, f2, f3, ... , f20;
dba_tab_col_statistics:
COLUMN_NAME NUM_DISTINCT LOW_VALUE HIGH_VALUE DENSITY NUM_NULLS HISTOGRAM
F1 5983 C3121515 C5040B214C0B 0,00016714 0 NONE
F2 1065 C102 C21336 0,000938967 0 NONE
F3 2 4E 59 0,5 0 NONE
F4....
F5....
There are two indexes on T:
normal IDX1(f1, f2)
normal IDX2(f3, f1, f2, f4)
(As we see in num_distinct, column f3 has 2 distinct values, like as "male/female". Also column f3 is not null)
And there is SQL:
select *
from T
where f1 = :v1
and f2 = :v2
Oracle chooses for this SQL skip scan on index IDX2(f3, f1, f2, f4)!
Why?
Obviously, that the best path is range scan on index IDX1(f1, f2)!
This is an excerpt from trace 10053:
Index Stats::
Index: IDX2 Col#: 3 1 2 51
LVLS: 3 #LB: 518022 #DK: 2175392 LB/K: 1.00 DB/K: 2.00 CLUF: 4977260.00
Index: IDX1 Col#: 1 2
LVLS: 3 #LB: 427734 #DK: 2247868 LB/K: 1.00 DB/K: 2.00 CLUF: 4757197.00
***************************************
SINGLE TABLE ACCESS PATH
-----------------------------------------
BEGIN Single Table Cardinality Estimation
-----------------------------------------
Column (#1): F1(NUMBER)
AvgLen: 7.00 NDV: 5983 Nulls: 0 Density: 1.6714e-04 Min: 172020 Max: 310327510
Column (#2): F2(NUMBER)
AvgLen: 4.00 NDV: 1065 Nulls: 0 Density: 9.3897e-04 Min: 1 Max: 1853
Table: T Alias: ISS
Card: Original: 39313156 Rounded: 6 Computed: 6.17 Non Adjusted: 6.17
-----------------------------------------
END Single Table Cardinality Estimation
-----------------------------------------
Access Path: TableScan
Cost: 416165.33 Resp: 416165.33 Degree: 0
Cost_io: 340305.00 Cost_cpu: 20531295599
Resp_io: 340305.00 Resp_cpu: 20531295599
kkofmx: index filter:"F1"=TO_NUMBER(:B1) AND "F2"=TO_NUMBER(:B2)
kkofmx: index filter:"F2"=TO_NUMBER(:B1)
kkofmx: index filter:"F1"=TO_NUMBER(:B1) AND "F2"=TO_NUMBER(:B2)
kkofmx: index filter:"F2"=TO_NUMBER(:B1)
Access Path: index (skip-scan)
SS sel: 1.5694e-07 ANDV (#skips): 2
SS io: 2.00 vs. table scan io: 340305.00
Skip Scan chosen
Access Path: index (SkipScan)
Index: IDX2
resc_io: 6.00 resc_cpu: 45154
ix_sel: 1.5694e-07 ix_sel_with_filters: 1.5694e-07
Cost: 6.17 Resp: 6.17 Degree: 1
Access Path: index (AllEqRange)
Index: IDX1
resc_io: 7.00 resc_cpu: 67135
ix_sel: 4.4487e-07 ix_sel_with_filters: 4.4487e-07
Cost: 7.25 Resp: 7.25 Degree: 1
****** trying bitmap/domain indexes ******
****** finished trying bitmap/domain indexes ******
Best:: AccessPath: IndexRange Index: IDX2
Cost: 6.17 Degree: 1 Resp: 6.17 Card: 6.17 Bytes: 0
Why does index IDX1 have more expensive costs than IDX2?