Re: Index choice

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

>
> 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 [oracle-l-bounce_at_freelists.org] on
> behalf of Mohamed Houri [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.freelists.org/webpage/oracle-l