Re: Basic Index Question - Why 5%?

You use a couple of different words here that I wouldn't really consider to be synonyms. Optimal and effective are quite different concepts. 5% may be some kind of optimal performance for an index but as to whether or not an index is effective depends on a vast number of factors. i.e. # of rows, rowsize, block size, SGA size, parallel query, and on and on.  You need to know a fair bit about the specific situation to decide how indexes affect performance.

Geoffrey Bray

In article <0a288596.b2aed15a_at_usw-ex0102-012.remarq.com>,   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!
>
>

Sent via Deja.com http://www.deja.com/
Before you buy. Received on Mon Jan 17 2000 - 14:21:28 CST