| 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@xxxxxxxxx> To: "Jonathan Lewis" <jonathan@xxxxxxxxxxxxxxxxxx> Cc: "ORACLE-L" <oracle-l@xxxxxxxxxxxxx> 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 -- //www.freelists.org/webpage/oracle-l