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

From: Mark W. Farnham <mwf_at_rsiz.com>
Date: Mon, 7 Dec 2015 14:04:22 -0500
Message-ID: <006e01d13122$1c008420$54018c60$_at_rsiz.com>

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_at_freelists.org [mailto:oracle-l-bounce_at_freelists.org] On Behalf Of Mohamed Houri Sent: Saturday, December 05, 2015 4:45 AM To: Stefan Koehler
Cc: Jonathan Lewis; l.flatz_at_bluewin.ch; 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_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  <https://twitter.com/MohamedHouri> Twitter      - MohamedHouri <https://twitter.com/MohamedHouri> 



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

Received on Mon Dec 07 2015 - 20:04:22 CET