Query not using the Bitmap index

[ssmith]

I have a table with 1498587 rows. The users query this data by year, and to improve this query I created a index on year, but the query didn't use this index because of the uniqueness of this index. Therefore I decided to create a bitmap index, but the query still doesn't use the bitmap index. Why?
This show how many records for each year.
ACT_YEAR COUNT(*)
---------- ----------
93 21858
94 168088
95 169920
96 163749
97 168262
98 180296
99 185229
2000 202371
2001 197180
2002 13665
27969

[Scorby]

a) Make sure you've analyzed the tables. You should do both an

"analyze table xxx compute statistics"

and

"analyze table xxx compute statistics for all indexed columns"

as the latter will give you the histogram data which Oracle will use when determining if a specific value is selective enough.

b) Check your query to see if it is using hard-coded values for the year or bind variables. If it is using bind variables, these will appear as :b1, :b2 etc rather than '99', '2001'. You can find out the SQL being executed by using SQL*Trace or by viewing the SQL through something like DBA Studio while it's executing.

If bind variables are being used, then Oracle will not be able to look at the actual data values when it decides how to do the query. It will have to make the decision based on the general selectivity of the column and this is quite low for the "year" column, so it's unlikely to use an index.

If the query is using bind variables, you could try switching it to hard-coded values. However, this may be difficult if the query is being issued through a front-end over which you have no control.

c) Check that the datatype of the column matches the data-type being passed in. If your year column is numeric but the query contains '99', you are comparing a string to a number and Oracle will have to do a data conversion. This wll suppress any index use.

d) Even after all this, with the distribution of the data that you have, I think Oracle may not choose to use the index. This is because there aren't that many distinct values.

A bitmap index is even less likely to be used in this circumstance than a btree because bitamp indexes are intended to be used in combination with other fields. If there are some other fields you can bring into the query which always have a particular set of values (and also have bitmap indexes), then this may help.

As a final resort, you can force Oracle to use the index with a hint:

select /*+ INDEX(...) */
...



[Edited by Scorby on 02-05-2002 at 09:38 AM]

[ssmith]

I have analyze the table.
This is the query used:
select * from schedule
where act_year=2001;

The act_year column is datatype number(22)!!

This is the explain plan:
Query Plan
----------------------------------------------------------------------------------------------------
SELECT STATEMENT Cost = 3628
TABLE ACCESS FULL FMC_SCHEDULE 1


I thought bitmap indexes should be used on low cardinality columns.

[Scorby]

You're right up to a point. Bitmap indexes are designed for combinations of low cardinality columns e.g. you have a query with:

year = 2000 = 500000 rows
product = 'X' = 400000 rows
account = 'Y' =100000 rows

But:
2000 + 'X' + 'Y' = 500 rows

But they're not designed to work with a single columns.

[Scorby]

Another option open to you would be to partition Schedule on year. A query like:

select * from schedule
where act_year=2001

would then show up as doing a full table scan but it would only actual scan one partition of the table.

[ssmith]

To partition the table was my next option, but the Patition option has not been installed.

Therefore, for a quick fix I wanted to use an index. I can't use hints in the query as the users use 'Crystal reports' to run queries.

I will use optimizer_mode=rule in the init.ora file, and create a 'normal' index on this column. This has worked.
I have found that the CBO is not as efficient as RBO!!

[tamilselvan]

The parameter b_tree_bitmap_plans must be set to TRUE in the init.ora file in order to use bit map indexes.
Also create_bitmap_area_size and bitmap_merge_area_size in the init.ora file should have a large value.

[Scorby]

I have never heard of this parameter and can't find it in my 8.1.6 database. Is it new ?

[jmodic]

Parameter B_TREE_BITMAP_PLANS is obsolete in 8i and above.

[Subha]

if your optimizer is rule bit mapindexes will not be used ??
check that