Re: RE: Re: Single-column vs composite index

From: Mohamed Houri <mohamed.houri_at_gmail.com>
Date: Sat, 5 Dec 2015 10:44:36 +0100
Message-ID: <CAJu8R6hZaFXzcM80qU01LCGKi3eE+4J4UiAbawwHy=be4dhfqw_at_mail.gmail.com>

Recently I have been asked by a customer to explain why a query that completes in few seconds sometimes hangs for ever. They said that the execution plan has not changed and this query almost always returns the same amount of rows (10 rows in average). The query has 3 predicates and there are several multi-columns indexes but there is no index including the 3 columns of the query where clause. The table has 500 Million of rows.

Looking carefully to the execution plan and the index used I have found that depending on the input values the used index is sometimes selective and sometimes not. i.e. sometimes the index feeds its parent table with a huge amount of rowids which the filter at the table level through all but 10 rows and hence the performance problem.

I suggested that if they want to avoid this sporadic performance pain they need to create and index with the 3 columns (of the query predicate part) or at least an index starting with the most selective column(s). As far as they were not allowed to add an extra index I did an investigation to find which index (starting by one of the 3 columns) is statistically the most selective one and fixed a SQL profile so that this query (coming from a third party software) will use this index. As such, we reduced the frequency at which this query was suffering performance pain.

But the bottom line here is : if we have created a 3 column index matching perfectly the predicate part we would have improved the effective index selectivity and reduced the time spent filtering from the big table

Best regards
Mohamed Houri

2015-12-04 21:28 GMT+01:00 Stefan Koehler <contact_at_soocs.de>:

> Yes, this is a very unexplored topic. Some vampires are also rumored to
> sparkle in the sun whereas i believe that they just burst into flames :-)
>
> > "l.flatz_at_bluewin.ch" <l.flatz_at_bluewin.ch> hat am 4. Dezember 2015 um
> 14:16 geschrieben:
> >
> > Scientifically it is not quite decided how silver works on vampires.
> There is a sad lack of volunteer vampires to test on.
> >
> >> Von : jonathan_at_jlcomp.demon.co.uk
> >> Datum : 04/12/2015 - 10:07 (GMT)
> >> An : oracle-l_at_freelists.org
> >> Betreff : RE: Re: Single-column vs composite index
> >>
> >> I've got to disagree with you on that one, Lothar: Silver bullets is
> werewolves, not vampires.
> >>
> >> Regards
> >> Jonathan Lewis
> --
> http://www.freelists.org/webpage/oracle-l
>
>
>

-- 

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.freelists.org/webpage/oracle-l

Received on Sat Dec 05 2015 - 10:44:36 CET