RE: Index Cost Calculation Mystery
- From: Jonathan Lewis <jonathan@xxxxxxxxxxxxxxxxxx>
- To: "usn@xxxxxxxxx" <usn@xxxxxxxxx>, "oracle-l@xxxxxxxxxxxxx" <oracle-l@xxxxxxxxxxxxx>
- Date: Wed, 26 Jun 2013 21:20:28 +0000
At the moment the only thing I can think of is that the table stats and the
index stats are not in synch.
The selectivity of indx2 (two columns) has to be derived from the column
num_distinct, but the selectivity of indx7 (single column) can be derived from
the distinct_keys in the index. Depending on the sample sizes and timing the
apparent number of distinct values (or the selectivity if there's a histogram
in plan) could vary dramatically between user_tab_columns and user_indexes
Regards
Jonathan Lewis
________________________________________
From: oracle-l-bounce@xxxxxxxxxxxxx [oracle-l-bounce@xxxxxxxxxxxxx] on behalf
of Martin Klier [usn@xxxxxxxxx]
Sent: 26 June 2013 21:23
To: oracle-l@xxxxxxxxxxxxx
Subject: Index Cost Calculation Mystery
Access Path: index (RangeScan)
Index: IDX2_AUFTRAG
resc_io: 186.00 resc_cpu: 1585830
ix_sel: 0.000034 ix_sel_with_filters: 0.000034
Cost: 94.49 Resp: 94.49 Degree: 1
Access Path: index (AllEqRange)
Index: IDX7_AUFTRAG
resc_io: 1027.00 resc_cpu: 8999338
ix_sel: 0.000241 ix_sel_with_filters: 0.000241
Cost: 521.97 Resp: 521.97 Degree: 1
...
...
Best:: AccessPath: IndexRange
Index: IDX2_AUFTRAG
Cost: 94.49 Degree: 1 Resp: 94.49 Card: 0.00 Bytes: 0
======10053==========
But why is the cost of a AllEqRange on a single column index
(IDX7_AUFTRAG) 521.97 and an index range scan on a multi-column index
(IDX2_AUFTRAG) has only cost 94.49?--
//www.freelists.org/webpage/oracle-l
Other related posts:
