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
