Re: When to Create Index?

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%
2. Tom Kyte, lately, has been doing a demo with function based indexes in just that type of situation.
3. Irrelevant.
4. Makes sense except for the "one or more" part.
5. 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)