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

Home -> Community -> Usenet -> c.d.o.misc -> Re: When to Create Index?

Re: When to Create Index?

From: DA Morgan <damorgan_at_x.washington.edu>
Date: Fri, 25 Feb 2005 08:15:13 -0800
Message-ID: <1109347934.596274@yasure>


dd wrote:

> Student Guide D33993, Introduction to Oracle9i:SQL Chap 12-18
> "DA Morgan" <damorgan_at_x.washington.edu> ??? news:1109263556.47250_at_yasure
> ???...
> 

>>dd wrote:
>>
>>
>>>In some Oracle's training material, it lists the condition to create
> 
> index
> 

>>>as follows:
>>>
>>>1. A column contains a wide range of values
>>>2. A column contains a large number of null values
>>>3. 1 or more columns are frequently used inWHERE clause
>>>4. The table is large and most queries are expected to retrieve less
> 
> than 2
> 

>>>to 4 % of the rows.
>>>
>>>I understand the rationale behind point 1, 3 only.
>>>
>>>For point 2, what do NULL values have to do with index?
>>>
>>>For point 4, I guess it is about benefit of index versus full table
> 
> scan -
> 

>>>if the queries return a lot of rows, full table scan isnt a bad idea.
> 
> But,
> 

>>>why it is 2-4% and not some other values?
>>
>>I don't think the advise is valid.
>>
>>1. An index on a column containing just
>>Y and N makes a lot of sense if the data were, for example 5% Y and 95%
>>N. Tom Kyte, lately, has been doing a demo with function based indexes
>>in just that type of situation.
>>
>>2. Irrelevant.
>>
>>3. Makes sense except for the "one or more" part.
>>
>>4. Table size is irrelevant. There was recently a long and painful
>>thread on just this issue in c.d.o.server participated in by Tom Kyte,
>>Jonathan Lewis, Richard Foote, and Howard Rogers with respect to small
>>lookup tables.
>>
>>I'd appreciate a link to the source of the advice so I can try to get
>>it correct.
>>
>>Thanks.
>>--
>>Daniel A. Morgan
>>University of Washington
>>damorgan_at_x.washington.edu
>>(replace 'x' with 'u' to respond)

Thanks.

-- 
Daniel A. Morgan
University of Washington
damorgan_at_x.washington.edu
(replace 'x' with 'u' to respond)
Received on Fri Feb 25 2005 - 10:15:13 CST

Original text of this message

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