Yes, i understand your point. I'm assumming that our column 'X' will be
distributed
most evenly. But you're right, if there is an almost-unique value in a low
cardinality column,
then maybe an index is worth doing.
"Dan Peacock" <peacocda_at_yahoo.com> wrote in message
news:67044b3b.0112051344.24ed212b_at_posting.google.com...
> That depends on how those values are distributed and how the rows are
> distributed in your blocks. Depending on your block size, you could
> have one of your 'X' rows in nearly every block. Thus, you would do a
> range scan of the index and then virtually do a full tablescan as you
> would still read in every block. Now, if you have skewed values, that
> is your column X has some values that take up most of the rows, then
> an index would help when going after the values that do not exist in
> most of the blocks (this is where histograms and the cost based
> optimizer come in handy!) Unfortunately, you've stumbled upon the
> biggest problem with using cardinality as a benchmark for the
> usefulness of an index. A classic example is indexing by gender. The
> cardinality will (generally) be 2. Which would lead you to think that
> an index wouldn't be a good idea. In the general population, this is
> true. Odds are, you will fetch all of the blocks anyway in a query
> using gender. Now, take the case of VMI or the Citidel. The
> cardinality is still two, but you and I both know that 50% of the
> school isn't made up of women cadets. In that case, an index would
> REALLY help when you were trying to access female cadet information,
> but hurt you when accessing male cadet information.
>
> I apologize for being a bit long winded, but you can't rely solely on
> cardinality when making index choices. You need to know the nature of
> your data and the distribution of values in the data set.
>
> HTH
>
> Dan Peacock
> DBA
> Wolverine World Wide
>
> "ayrobins" <anthony_at_lumos.com> wrote in message
news:<gurP7.80$6e6.12047_at_dca1-nnrp2.news.digex.net>...
> > I am using the standard edition of oracle. I realize that bitmap
indexes
> > aren't supported.
> >
> > I have a table that can contain around 50 million records and can be
looked
> > up by column
> > X. X has a cardinality of 20. i.e. it can contain 20 distinct values.
A
> > lookup by 'X' can
> > will be done often.
> >
> > Does it make sense to not use an index at all. Or is a Btree index
better
> > then nothing.
Received on Wed Dec 05 2001 - 17:07:43 CST
