Index choice

From: Mohamed Houri <mohamed.houri_at_gmail.com>
Date: Mon, 12 Jan 2015 20:33:04 +0100
Message-ID: <CAJu8R6i8Jj9Fn6hu_b+ANDNKRgD9tL5g9gzR52R8W7PN08ANWQ_at_mail.gmail.com>

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>


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

Received on Mon Jan 12 2015 - 20:33:04 CET