Hi Mohamed,In both indexes col_c will be used as filter and not for access. The position in the index, as long as it is after col_a,col_b makes little difference. What should matter is the length and the number of the leaf blocks I guess. Thus your result does surprise me. Did you check optimizer_index_cost_adj? A small value can sometimes even minor differences in index cost. The whole explanation is in Jonathans book (Cost-based Oracle) page 83. (At least in my issue).
Thanks Lothar Am 14.01.2015 um 20:09 schrieb Mohamed Houri:
Dear list I am back for this issue to give you a feedback Let me summarise very quickly select col1,col2,coln from table where col_a = val_a and col_b = val_ba and *col_c <> 0*;CBO is using an index index_1(col_a, col_b, col_x, col_y , col_z) with a filter on table using *col_c*The client want to use the index_2(col_a, col_b, *col_c*, col_v)1) Changing the clustering factor has not made the desired cursor (without a filter on the table) to be used 2) reversing the order of the two fist column is not acceptable by this client 3) creating a new index on (cola, colb, colc) has not been accepted by this client 4) I have not investigated the option of set_table_prefs for the table to change the "history"However, looking again at the 10053 trace file one thing attracted my attention when analysis table selectivity there was a line on *col_b* which says */"out of range pred"/* (sorry working from memory)This line suggests me to look at the low and high value of *col_b*. Result is that *val_b* is > high_valueI 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_2The 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 adaptedThe 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?Best regards Mohamed Houri PSIf you need extra select from user_indexes then I will provide you with that information tomorrow evening2015-01-12 22:15 GMT+01:00 Jonathan Lewis <jonathan@xxxxxxxxxxxxxxxxxx <mailto:jonathan@xxxxxxxxxxxxxxxxxx>>:A third option to investigate is to reverse the first two columns of one of the indexes as this may change the clustering factor enough to bypass the problem. A fourth option would be to use the set_table_prefs for the table to change the "history" that Oracle remembers as it is calculating the clustering_factor - this may affect both clustering_factors in a suitable way. Regards Jonathan Lewis http://jonathanlewis.wordpress.com @jloracle ------------------------------------------------------------------------ *From:* oracle-l-bounce@xxxxxxxxxxxxx <mailto:oracle-l-bounce@xxxxxxxxxxxxx> [oracle-l-bounce@xxxxxxxxxxxxx <mailto:oracle-l-bounce@xxxxxxxxxxxxx>] on behalf of Mohamed Houri [mohamed.houri@xxxxxxxxx <mailto:mohamed.houri@xxxxxxxxx>] *Sent:* 12 January 2015 19:33 *To:* ORACLE-L *Subject:* Index choice I visited today a customer which has a critical query on a table with more than 400 millions of rows. The query is of the following form: select col1, col2, coln from table where col_a = val_a and col_b = val_ba and col_c <> 0; There are several indexes on this table among them there are two particular ones (I am working from memory because I couldn't have access to oracl-list because of the client restriction) index_1(col_a, col_b, col_x, col_y , col_z) index_2(col_a, col_b, col_c, col_v) The CBO decided to use the first index *(index_1*) with an access on (col_a, col_b) and *a costly filter* on *table* (using col_c). While the customer is very happy when the query uses the*index_2* with access on (col_a, col_b) and filter on col_c all those predicates applied only on the index_2. Which means there is no filter on table at all. When I looked at the corresponding 10053 trace file I found that both indexes have the same cost but a slightly different clustering factor and *resc_cpu* (they are vey close but the clustering factor of index_1 is better than the clustering factor of index_2) *col_c* has a Height Balanced Histogram but this might not help because I have 3 predicates. Extended stats will not help here because there is an inequality on col_c In my opinion they remain two options to make the CBO choosing index_2 instead of index_1 * set manually (using dbms_stat) the clustering factor of index_2 so that it will be less than the clustering factor of index_1 * compress the index_2 so that the number of leaf block will be reduced and hence the cost will also be reduced What do you think? Sorry to do not post the corresponding executions plans. I summarized the issue using what I remember from this morning issue Thanks in advance PS : I have proposed to create a virtual column virt_col_c(case when col_c <> 0 then col_c else null end) and create an index on (col_a,col_b, virt_col_c)and change the query to select col1, col2, coln from table where col_a = val_a and col_b = val_ba and col_c = virt_col_c; Unfortunately it is impossible to change the code of the application--Houri Mohamed Oracle DBA-Developer-Performance & Tuning Member of Oraworld-team <http://www.oraworld-team.com/> Visit My - Blog <http://www.hourim.wordpress.com/> Let's Connect -<http://fr.linkedin.com/pub/mohamed-houri/11/329/857/>_Linkedin Profile <http://fr.linkedin.com/pub/mohamed-houri/11/329/857/>_ My Twitter <https://twitter.com/MohamedHouri> -MohamedHouri <https://twitter.com/MohamedHouri> -- Houri Mohamed Oracle DBA-Developer-Performance & Tuning Member of Oraworld-team <http://www.oraworld-team.com/> Visit My - Blog <http://www.hourim.wordpress.com/>Let's Connect -<http://fr.linkedin.com/pub/mohamed-houri/11/329/857/>_Linkedin Profile <http://fr.linkedin.com/pub/mohamed-houri/11/329/857/>_My Twitter <https://twitter.com/MohamedHouri> -MohamedHouri <https://twitter.com/MohamedHouri>
-- --- Diese E-Mail ist frei von Viren und Malware, denn der avast! Antivirus Schutz ist aktiv. http://www.avast.com