Re: Index choice
- From: Mohamed Houri <mohamed.houri@xxxxxxxxx>
- To: Jonathan Lewis <jonathan@xxxxxxxxxxxxxxxxxx>
- Date: Wed, 14 Jan 2015 20:09:47 +0100
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>:
>
> 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>
>
>
--
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:
