Re: Index choice

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_value
>
> 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?
>
>
> Best regards
> Mohamed Houri
>
> PS
> If you need extra select from user_indexes then I will provide you
> with that information tomorrow evening
>
> 2015-01-12 22:15 GMT+01:00 Jonathan Lewis <jonathan_at_jlcomp.demon.co.uk
> <mailto:jonathan_at_jlcomp.demon.co.uk>>:
>
>
> 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
> _at_jloracle
> ------------------------------------------------------------------------
> *From:* oracle-l-bounce_at_freelists.org
> <mailto:oracle-l-bounce_at_freelists.org>
> [oracle-l-bounce_at_freelists.org
> <mailto:oracle-l-bounce_at_freelists.org>] on behalf of Mohamed Houri
> [mohamed.houri_at_gmail.com <mailto:mohamed.houri_at_gmail.com>]
> *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

--
http://www.freelists.org/webpage/oracle-l