RE: Index choice

From: Jonathan Lewis <jonathan_at_jlcomp.demon.co.uk>
Date: Mon, 12 Jan 2015 21:15:23 +0000
Message-ID: <CE70217733273F49A8A162EE074F64D928268128_at_EXMBX01.thus.corp>

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)