CODEWITHSUNDEEP
databaseoracle-databaseoracle11gindexing
user1261104
user1261104

Reputation: 325

How to create bitmap index for this query

I'm trying to create a bitmap index for this query:

SELECT "TAB2"."TAB2ID","TAB1"."TAB1ID"
FROM "TAB1","TAB2"
WHERE ((("TAB1"."YESNO" = 'Y' ) 
AND ("TAB2"."LOCID" = "TAB1"."LOCID" ) ) 
AND ("TAB2"."YESNO" = 'Y' ) ) 
ORDER BY "TAB1"."LOCNO";

I've tried multiple bitmap indexes, but none of them seem to get used, even if I try to give the query a hint to use the bitmap index. Even a index on just tab2 doesn't seem to do anything.

This is one that I tried but that did nothing, even after gathering index and table stats:

CREATE BITMAP INDEX TAB2_TAB1_BIT_IDX 
ON TAB2(TAB2.YESNO,TAB1.YESNO)
FROM    TAB1        ,       TAB2 
WHERE   TAB2.LOCID = TAB1.LOCID

Tab1 contains 2 N and 29 Y, Tab2 contains 30000 'N' and 240000 'Y'. What should be the code for the bitmap index, and are there other ways to optimize this query?

Upvotes: 2

Views: 1980

Answers (1)

Wernfried Domscheit
Wernfried Domscheit

Reputation: 59456

It must be this one:

CREATE BITMAP INDEX TAB1_BIT_IDX  ON TAB1 (YESNO);
CREATE BITMAP INDEX TAB2_BIT_IDX  ON TAB2 (YESNO);

General note: usually a single Bitmap Index on a table does not help so much. The big gain of Bitmap Indexes you get when you define several Bitmap Indexes. A single Bitmap Index has low selectivity but the combination of several Indexes should be selective. In this case you use Bitmap Indexes properly!

Upvotes: 1

Related Questions