Dear list
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@xxxxxxxxxxxxxxxxxx <mailto:jonathan@xxxxxxxxxxxxxxxxxx>>:
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
<mailto:oracle-l-bounce@xxxxxxxxxxxxx>
[oracle-l-bounce@xxxxxxxxxxxxx
<mailto:oracle-l-bounce@xxxxxxxxxxxxx>] on behalf of Mohamed
Houri [mohamed.houri@xxxxxxxxx <mailto: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>
--
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>
