Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.server -> Re: When to apply an index?

Re: When to apply an index?

From: Richard Foote <richard.foote_at_bigpond.com>
Date: Mon, 22 Jul 2002 19:41:59 +1000
Message-ID: <CkQ_8.41122$Hj3.123478@newsfeeds.bigpond.com>


Hi DB,

Image you're reading a book (History of Rock Music) but you're only interested in David Bowie. How can you guarantee you read every detail on David Bowie ?

One way is to read the whole book but that could take you days and you have to read everything from Abba to Zappa when you're only interested in Mr Bowie.

Another alternative would be to go to the back of the book, read the index (ah- ha !) which you will notice is alphabetically ordered so you can very efficiently find the part of the index that references David Bowie. There you will find a list of all pages that mentions David Bowie. By popping directly to the pages of interest (because it's David Bowie, he would be mentioned quite a bit) but after only a relatively small period of time you have read everything of interest. Because the index contains such a large list of artists and because the list of pages that mentions David Bowie is relatively small, the index was most useful and efficient to use.

Now image you're reading another book (Stardust: the Life and Times of David Bowie). Same thing, you're only interested in pages containing David Bowie. This time when you check out the index, there are not a great many other artists listed, and the index entry for David Bowie lists virtually every page. In this case, it's far more efficient to just read the whole book than keep flipping back and forth using the index.

Oracle works in a similar way. *Generally* speaking, if you're after a relatively small subset of all the data (meaning the index probably has high cardinality) then an index could be useful. If you are after a *relatively* large subset of the data (hence the index probably has low cardinality) then the index probably will not be useful and it's better to just read the whole table.

This is a very general overview. It actually *depends* on a whole stack of things but the above is at least a small step in understanding how it all works.

Cheers

Richard

"dbuckingham" <member_at_dbforums.com> wrote in message news:3d3bb349$1_at_usenetgateway.com...
> Is it better to apply indexes to tables that have many different random
> values or is it better to apply to tables that only have a small number
> of values (large table but a lot of the same value)?
>
> Dan.
>
>
>
> --
> Daniel Buckingham
> Technical Consultant
> Mercia Software
>
> Posted via dBforums
> http://dbforums.com
Received on Mon Jul 22 2002 - 04:41:59 CDT

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US