Re: Basic Index Question - Why 5%?

From: Connor McDonald <connor_mcdonald_at_yahoo.com>
Date: Mon, 17 Jan 2000 19:34:38 +0800
Message-ID: <3882FE4E.15CB@yahoo.com>

MohammedB 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!

Often the break even is more than 5% but the philosophy is that a table scan can use multiread, that is, read multiple blocks with one trip to the disks whereas an index is block-by-block.

This has changed a little in more recent releases where indexes can also be scanned in multiread fashion.

HTH
--

---

Connor McDonald
"These views mine, no-one elses etc etc" connor_mcdonald_at_yahoo.com

"Some days you're the pigeon, and some days you're the statue." Received on Mon Jan 17 2000 - 05:34:38 CST