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>
