Re: Index choice

are you sure that you really need histograms on *col_c?*

On Mon, Jan 12, 2015 at 11:02 PM, Lothar Flatz <l.flatz_at_bluewin.ch> wrote:

> Hi Mohamed,
>
> From the two options you named I would go for compression because you do
> not have to repeat it after stats generation.
> The quality of compression will depend on the Selectivity of leading
> columns. That could no be so high, otherwise col_c would not make that
> difference.
> I ask however why you don't want to create an index (col_a, col_b,
> col_c)? Than compress it. It will be smaller and more likely be selected.
> I wonder however, why the two Indexes are seen as equal by the optimizer.
> The estimate can't be correct, otherwise the plan would be better. Thus, I
> wonder where the Optimizer fails. Maybe extended stats on (col_a, col_b)
> would still help,
>
> Thanks
>
> Lothar
>
> Am 12.01.2015 um 20:33 schrieb Mohamed Houri:
>
> 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>
>
>
>
> --
>
>
>
>
>
>
>
> ------------------------------
> <http://www.avast.com/>
>
> Diese E-Mail ist frei von Viren und Malware, denn der avast! Antivirus
> <http://www.avast.com/> Schutz ist aktiv.
>
>

-- 
Best regards,
Sayan Malakshinov
Senior performance tuning engineer
PSBank
http://orasql.org

--
http://www.freelists.org/webpage/oracle-l