Re: low cardinality columns and indexing.

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 - 15:44:08 CST