RE: Index choice
- From: Jonathan Lewis <jonathan@xxxxxxxxxxxxxxxxxx>
- To: ORACLE-L <oracle-l@xxxxxxxxxxxxx>
- Date: Mon, 12 Jan 2015 21:15:23 +0000
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
@jloracle
________________________________
From: oracle-l-bounce@xxxxxxxxxxxxx [oracle-l-bounce@xxxxxxxxxxxxx] on behalf
of Mohamed Houri [mohamed.houri@xxxxxxxxx]
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>
Other related posts:
