This *may* have to do with the cluster factor for your actual choices as
opposed to the average cluster factor.
Some of the triples=true row sets might be in a single row containing block,
while some may be highly scattered.
Just one possible answer to your situation.
mwf
From: oracle-l-bounce@xxxxxxxxxxxxx [mailto:oracle-l-bounce@xxxxxxxxxxxxx] On
Behalf Of Mohamed Houri
Sent: Saturday, December 05, 2015 4:45 AM
To: Stefan Koehler
Cc: Jonathan Lewis; l.flatz@xxxxxxxxxx; ORACLE-L
Subject: Re: RE: Re: Single-column vs composite index
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@xxxxxxxx>:
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@xxxxxxxxxx" <l.flatz@xxxxxxxxxx> 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@xxxxxxxxxxxxxxxxxx
Datum : 04/12/2015 - 10:07 (GMT)
An : oracle-l@xxxxxxxxxxxxx
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
