In 9i: selectivty of index(col1, col2, col3)= density (co11) x denisty (col2) x denisity(col3);
In 10g and 11g selectivity of index(col1, col2, col3) = 1/DISTINCT_KEYS where DISTINCT_KEYS is from index stats obtainable through dba_indexes.
Here is the test case which mimics the production able and the problem sql.
----- begin of the test case ----
drop table test;
create table test
as
select level id,
trunc(mod(level,80)) id1,
20*trunc(mod(level, 40)) id2,
1 id3,
trunc(mod(level,50)) id4
from dual
connect by level <=1000000;
create index test_ix1 on test(id1, id2, id3);
exec dbms_stats.gather_table_stats(user,'test', method_opt=>'FOR ALL COLUMNS size 1', cascade=>true);
alter session set tracefile_identifier = test;
ALTER SESSION SET EVENTS='10053 trace name context forever, level 1';
explain plan for
select id
from test
where id1 = 20
and id3=1
and id2 = 400;
ALTER SESSION SET EVENTS '10053 trace name context off';
Exit
------ end of test case -----------------------
#### 9.2.0.8 10053 trace excerpt
***************************************
SINGLE TABLE ACCESS PATH
Column: ID1 Col#: 2 Table: TEST Alias: TEST
NDV: 80 NULLS: 0 DENS: 1.2500e-02 LO: 0 HI: 79
NO HISTOGRAM: #BKT: 1 #VAL: 2
Column: ID3 Col#: 4 Table: TEST Alias: TEST
NDV: 1 NULLS: 0 DENS: 1.0000e+00 LO: 1 HI: 1
NO HISTOGRAM: #BKT: 1 #VAL: 2
Column: ID2 Col#: 3 Table: TEST Alias: TEST
NDV: 40 NULLS: 0 DENS: 2.5000e-02 LO: 0 HI: 780
NO HISTOGRAM: #BKT: 1 #VAL: 2
TABLE: TEST ORIG CDN: 1000000 ROUNDED CDN: 313 CMPTD CDN: 313
Access path: tsc Resc: 857 Resp: 847
Access path: index (equal)
Index: TEST_IX1
TABLE: TEST
RSC_CPU: 711380 RSC_IO: 82
IX_SEL: 0.0000e+00 TB_SEL: 3.1250e-04
BEST_CST: 83.00 PATH: 4 Degree: 1
#### 10.2.0.4 10053 trace excerpt
***************************************
SINGLE TABLE ACCESS PATH
-----------------------------------------
BEGIN Single Table Cardinality Estimation
-----------------------------------------
Column (#2): ID1(NUMBER)
AvgLen: 3.00 NDV: 81 Nulls: 0 Density: 0.012346 Min: 0 Max: 79
Column (#4): ID3(NUMBER)
AvgLen: 3.00 NDV: 1 Nulls: 0 Density: 1 Min: 1 Max: 1
Column (#3): ID2(NUMBER)
AvgLen: 4.00 NDV: 40 Nulls: 0 Density: 0.025 Min: 0 Max: 780
Table: TEST Alias: TEST
Card: Original: 1003092 Rounded: 310 Computed: 309.60 Non Adjusted: 309.60
-----------------------------------------
END Single Table Cardinality Estimation
-----------------------------------------
Access Path: TableScan
Cost: 730.88 Resp: 730.88 Degree: 0
Cost_io: 700.00 Cost_cpu: 284170229
Resp_io: 700.00 Resp_cpu: 284170229
Access Path: index (AllEqRange)
Index: TEST_IX1
resc_io: 3173.00 resc_cpu: 27721329
ix_sel: 0.0125 ix_sel_with_filters: 0.0125
Cost: 3176.01 Resp: 3176.01 Degree: 1
Best:: AccessPath: TableScan
Cost: 730.88 Degree: 1 Resp: 730.88 Card: 309.60 Bytes: 0
Note:
select index_name, distinct_keys from user_indexes where index_name='TEST_IX1';
INDEX_NAME DISTINCT_KEYS
------------------------------ -------------
TEST_IX1 80
#### 11.2.0.1 10053 trace excerpt
***************************************
SINGLE TABLE ACCESS PATH
Single Table Cardinality Estimation for TEST[TEST]
ColGroup (#1, Index) TEST_IX1
Col#: 2 3 4 CorStregth: 40.00
ColGroup Usage:: PredCnt: 3 Matches Full: #1 Partial: Sel: 0.0125
Table: TEST Alias: TEST
Card: Original: 1000000.000000 Rounded: 12500 Computed: 12500.00 Non Adjusted: 12500.00
Access Path: TableScan
Cost: 629.80 Resp: 629.80 Degree: 0
Cost_io: 617.00 Cost_cpu: 283372261
Resp_io: 617.00 Resp_cpu: 283372261
ColGroup Usage:: PredCnt: 3 Matches Full: #1 Partial: Sel: 0.0125
ColGroup Usage:: PredCnt: 3 Matches Full: #1 Partial: Sel: 0.0125
Access Path: index (AllEqRange)
Index: TEST_IX1
resc_io: 3173.00 resc_cpu: 27721329
ix_sel: 0.012500 ix_sel_with_filters: 0.012500
Cost: 3174.25 Resp: 3174.25 Degree: 1
Best:: AccessPath: TableScan
Cost: 629.80 Degree: 1 Resp: 629.80 Card: 12500.00 Bytes: 0
Notice the ix_sel line in the above 10053 trace excerpt; for 10g and 11g it equal to 1/80. for 9i seems computed through multiplying selectivity of eeach column.
Update:
Came accross Jonathan Lewis's comment, and came to know this CBO behavior changes acctually from 10.1.0.4 ie. 10.1.0.4 is still similar to 9i behavior.
Below is his comment: (http://kr.forums.oracle.com/forums/thread.jspa?messageID=3153224)
--------
For a single-table query with the predicate
(full list of index columns) = (set of values)
The selectivities vary across version as follows:
11.1.0.6: Table selectivity and index selectivity given by distinct_keys in index
10.2.0.1: Table selectivity given by product of column selectivities, index selectivity given by distinct_keys in index
10.1.0.4: Table selectivity and index selectivity given by product of column selectivities
------------
JL's original post about this behavior: http://jonathanlewis.wordpress.com/2008/03/11/everything-changes/
