Oracle did not choose to not use an index because you only had 7 distinct values for 500,000 records. It chose not to use an index because it didn't show, according to the statistics it had available to it and the various parameters you have set within the initialization file + defaults, that a particular index was selective enough to select over a full table scan.
One potential reason for this is that you may have not computed histograms for the table column(s) in your index.
I have a TAR in with Oracle right now on a bitmapped index issue that is relevant here.
I have a table w/ about 4 million rows. I created a bitmapped index on a column called source_system. When I analyze the table, it determines that the cardinality of the table is 8 (#distinct values). In the absense of a histogram, Oracle assumes an equal distribution of values so in this case, Oracle assumes (4000000/8 or 500000 rows per value or 12.5% of the table per value - obviously not very selective). In this case if you do the following:
select distinct(source_system), count(*)
group by source_system
You get the following results:
tms_frc 18266 <- .43%
tms_clr 64305 <- 1%
fas 139609 <- 3%
ccss 305756 <- 7%
tms_fas 309575 <- 7%
corp 626667 <-14%
uty 1041874 <-23%
tms_ren 1942034 <- 44%
Obviously, this index is very selective - of the 8 values, 82% of the total table volume is in 3 of the 8 columns. Here you have a prime candidate for a bitmapped index. Very low cardinality(small number of distinct values) and very high selectivity(percentage of table referenced by a particular value).
The problem here is that without computing a histogram for the source_system column on the table, Oracle is not going to choose this index because each value is being interpreted as holding 12.5% of the total row distribution. Once a histogram has been computed for the column, Oracle ->should<- choose the index when the user is constraining on the source_system column with one of the most selective values.
There are still a number of parameters that can be set in the database which will lead to an adjustment in the cost of this particular path between all of the available paths which still may lead Oracle to believe another path is better. If you have done all of this and are still not getting the appropriate index, you may need to:
1) Check your sql and make sure that you are not doing
something that is suppressing the index, like an UPPER
function or something of that nature. There are a
number of things that can happen (like using a function
in the where clause) which will supress an index on the
column that the function is applied to.
2) look at your parameters overall and see if you have
something set which is causing Oracle, in general, to
discount the cost of a full scan or another access path to
Senior Database Administrator
Click Here to Expand Forum to Full Width