Re: Basic Index Question - Why 5%?

One other thing.. An index lookup is not neccesarily just 2 reads. In a b-tree structure, a block can point you to another block first before pointing to you to the block you want on disk. In general I think it's at least 3 I/O's. One for the header, one for the leaf, and then one to disk. If things are fragmented badly, it could be much worse. Also, that rule of thumb seems a little narrow. I've heard (as a rule of thumb), more like 30% or more before a FTS can be faster, but as another posted pointed out, there are a LOT of factors involved.

• Dc.

On Mon, 17 Jan 2000 10:19:52 -0800, MohammedB <mohammedbNOmoSPAM_at_iadb.org.invalid> wrote:

>I'm having a little trouble trying to conceptulize this scenario.
>
>I've read that index use is optimal when selecting 5% or less records
>in a table. I would have thought that anything less than 49% would be
>effective since you'd have one read of the index and one read to
>retrieve the corresponding record (reads < 98% of total data). I'm
>talking about a basic equality query using b-tree indexes and no PQ.
>Would'nt this be more efficient than a full-table scan (reads = 100% of
>data)? Also, why is the break even point around 5%? Is their some
>sort of optimization theory involved here?
>
>Confused.
>
>mkb
>
>
>
>* Sent from RemarQ http://www.remarq.com The Internet's Discussion Network *
>The fastest and easiest way to search and participate in Usenet - Free!
>
Received on Wed Jan 19 2000 - 21:53:02 CST