Skip to Main Content

Oracle Database Discussions

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

Bitmap Index Not getting used

IshanOct 20 2012 — edited Oct 20 2012
Hi,

I am using ORACLE 11gR1.

I was running a test on a huge table with a bitmap index on one of the columns. But bitmap index is not getting used. Below are the test details.

create table test (col1 number, col2 number);

begin
for i in 1..1000000
loop
if mod(i,2) = 0 then
insert into test values(i, 'Y');
else
insert into test values(i, 'N');
end if;
end loop;
end;
COMMIT;

The intention here is to have only two distinct values in the entire table. Based on these values I will not build BITMAP index on col2.

CREATE BITMAP INDEX BITMAP_TEST on TEST(col2) COMPUTE STATISTICS;

Now when I run the below query, it doesn't uses BITMAP index. Instead the explain plan shows a full table scan.

select * from test where col2 = 'Y';

Now when I force ORACLE to use index through hint, the cost is too high while using the bitmap index(probably why the ORACLE chose not to use the index at the first stage).

I have read somewhere, that BITMAP index is useful when we have more than 1 or 2 bitmap indexes on other columns of the same table as well And the query should be like

select * from table where col1 = 'Y' and sex = 'F';

In this case oracle will use BITMAP but not in the case where there is only one column that has BITMAP index.

Considering all the factors stated above, is there any way I can fine tune my original query?

select * from test where col2 = 'Y';

Please suggest a work around
Comments
Locked Post
New comments cannot be posted to this locked post.
Post Details
Locked on Nov 17 2012
Added on Oct 20 2012
7 comments
1,827 views