Re: Index choice
Date: Thu, 15 Jan 2015 11:49:25 -0000
Message-ID: <D3FF387C438047A3818D0F2CE694D617_at_Primary>
| the same cost
Cost is reported (in the trace) to 2 d.p. so the difference could be in later d.p. due to differences in CPU cost.
| the same effective index_selectitiy (ix_sel_with_filter)
Would expect this
| resc_cpu (index_3) < resc_cpu (index_2)
This may be sufficient to account for the choice if the resc_io is the same for the two indexes
|
| The avg_key_per_date_block is41 for index_3 and 31 for index_2
Doesn't tell us anything because we don't know how many (complete) keys there are
in each index. What matters is the ix_sel_with_filter *clustering_factor
| The leaf_blocks of index_3 > leaf_blocks of index_2
The component of cost due to leaf blocks is often much smaller than the component due to the clustering factor
so this doesn't tell us anything
| *Questions:*
|
| 1) What extra information has been used by Oracle to choose index_3
instead| of index_2
Clustering_factor of the indexes is probably the most significant
| 2) does the influence of a position of the a column in an index decreases
when it is applied against an inequality predicate?
In principle no - ONCE you've got past all the equality predicates.
But I think it's almost automatic to think that the more columns
you have in an index the
higher the clustering_factor would be, which leads to a feeling
that the index with the predicate
against the 5th column "ought" to be more expensive than the index
where it's the 4th column.
Regards
Jonathan Lewis
http://jonathanlewis.wordpress.com/all-postings
Author: Oracle Core (Apress 2011)
http://www.apress.com/9781430239543
- Original Message ----- From: "Mohamed Houri" <mohamed.houri_at_gmail.com> To: "Jonathan Lewis" <jonathan_at_jlcomp.demon.co.uk> Cc: "ORACLE-L" <oracle-l_at_freelists.org> Sent: Wednesday, January 14, 2015 7:09 PM Subject: Re: Index choice
|
| I re gathered statistics and when val_b fails into the
low_value-high_value
| interval..........
|
| a new index* index_3* (col_a, col_b, col_h, col_k, *col_c*) *without a
| filter on the table*
|
| The client is Ok with this index.
|
| But my curiosity suggested me to generate a new 10053 trace file to
| understand why the CBO has chosen index_3 instead of index_2
|
| The col_c in index_3 is at the end of the index while it is right at the
| 3rd position in the index_2. Logically index_2 seems more adapted
|
| The 10053 trace file shows
|
| the same cost
| the same effective index_selectitiy (ix_sel_with_filter)
| resc_cpu (index_3) < resc_cpu (index_2)
|
| The avg_key_per_date_block is41 for index_3 and 31 for index_2
| The leaf_blocks of index_3 > leaf_blocks of index_2
|
| *Questions:*
|
| 1) What extra information has been used by Oracle to choose index_3
instead
| of index_2
| 2) does the influence of a position of the a column in an index decreases
| when it is applied against an inequality predicate?
|
No virus found in this message.
Checked by AVG - www.avg.com
Version: 2015.0.5645 / Virus Database: 4260/8933 - Release Date: 01/15/15
-- http://www.freelists.org/webpage/oracle-lReceived on Thu Jan 15 2015 - 12:49:25 CET