Re: Index choice

using col_c as a filter means that all leaf blocks will be scanned in a range of defined by col_a, col_b.
If that range is estimated very small, differences in index cost will also be small.
Check by applying cardinality feedback if the condition where col_a = val_a

    and col_b = val_ba by itself (without col_c) is estimated correctly by the optimizer.

Thanks

Lothar
Am 14.01.2015 um 23:09 schrieb Lothar Flatz:
> 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_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>
>>
>
>
> --
>
>
>
>
>
>
> ------------------------------------------------------------------------
> <http://www.avast.com/>
>
> Diese E-Mail ist frei von Viren und Malware, denn der avast! Antivirus
> <http://www.avast.com/> Schutz ist aktiv.
>
>

-- 






---
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