Re: index usage

From: Holger Baer <holger.baer_at_science-computing.de>
Date: Thu, 20 Jan 2005 15:47:57 +0100
Message-ID: <csogau$r6v$1@news.BelWue.DE>

Richard Foote wrote:
> "Holger Baer" <holger.baer_at_science-computing.de> wrote in message
> news:csm2hc$ssu$1_at_news.BelWue.DE...
>

>>hastenthunder wrote:
>>
>>>Hello,
>>>
>>>I've read many documentations online stating to only create an index if
>>>queries against this table frequently retrieve less than 15% of the rows.
>>>However, if the query returns, say, 40% of the rows, wouldn't indexing 
>>>the
>>>column still help by cutting the work by roughly half?
>>>
>>>
>>>hastenthunder
>>>
>>>
>>
>>The number actually varies (down to 2% in some cases) and, as they say, 
>>depends
>>on many factors.

>
>
> Hi Holger,
>
> And much lower than 2% in other cases ...

What I meant was, that the numbers I've read sofar went down to 2%, I didn't intend to create a lower bound, but thanks for the correction.

>
>

>>But just think about it:
>>If you're going to read about half a book - would you really go through 
>>the index
>>or would you rather just leafe through the book and skip those pages that 
>>don't
>>interest you?

>
>
> The point that some people find hard "to picture" is that to read half a
> book, you might need to actually access *every* page. Why, because you might
> only be interested on average with the information on 1/2 of every page. 1/2
> a book doesn't necessarily mean 1/2 the pages but 1/2 of every page.
>
> With Oracle "pages", this is more likely the higher the selectivity and the
> worse the CF of the table in relation to the required data set.
>
> Cheers
>
> Richard
>
>

I've still got to hone my skills in writing analogies in a language that is not my own, so while struggling for the right words I missed this important part. ;-(

Thanks.

Oh, btw, I seem to remember that you once did a presentation how block splitting in indexes work. Is that still available somewhere? Any pointers would be much appreciated.

Holger Received on Thu Jan 20 2005 - 08:47:57 CST