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
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
As long as the table is not updated /inserted frequently, bit map index is OK.
If your result set is going to be 12% of the rows from the 500,000 rows table, then I would not allow CBO to use neither bit map index nor the B-Tree Index, because it requires more I/O for reading Index and data blocks to figure out the result set. In this case I would choose full table scan, because the data is going to be present in LRU for longer time.
That's a very good point, I should have added that. Typically bitmapped indexes are used more in a DSS environment than in a OLTP environment because they slow down updates and also the locking strategy is more restrictive. This doesn't tend to be a big issue in DSS systems because usually aren't updated during the day, at least to a large extent.
Senior Database Administrator
regarding 'wrong statistics' issue:
no parameters or other magic prevented oracle from using index except of statistics wich indicated index selectivity was low. When I deleted test records and created 'correct' statistics, it used index very well.
From my understanding, histograms would not help(?) here, because I used bind variables(every one should use them) and Oracle is not rebuilding(?) execution plans every time execute is called(it should cost to much. I put a lot of ? marks just because Oracle docs which try to describe CBO are for management but not for programmers - to much text and too much inprecise language).
In general I think CBO is for only DSS applications, when users submits any query to the db and usually those queries have literals instead of bind variables. Then parsing of sql is not a big deal and all CBO advantages(including histograms)can be used. For OLTP application all sql is tuned manually anyway and CBO can only complicate things.
(comments on all errors in this text are wellcomed, I've spent too much time in fighting CBO after decided to use it for OLTP)
select customer_name, customer_number
where source_system = 'tms_frc' (only 0.4 % of the table)
I would suggest create a normal B-tree index on SOURCE_SYSTEM. And use hint clause in the SELECT to force CBO to use the Index.
DO NOT ASSUME that CBO will always do a fine job. If you know your data and SQL, fine tune it as much as possible by adding HINTs in the SELECT statement, because it works for 1M rows as well as 10M rows.