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)
Received on Fri Feb 25 2005 - 04:21:22 CST
