Expert Indexing in Oracle Database 11g: Maximum Performance for Your Database

Creating a Bitmap Join Index

Creating a bitmap join index is similar to creating a normal bitmap index in that you need the BITMAP keyword, but different in that you also need the FROM and WHERE clauses. For example,

CREATE BITMAP INDEX BILLING_FACT_BJIX01 ON BILLING_FACT (GEO.GEO_ID) FROM BILLING_FACT BF, GEOGRAPHY_DIMENSION GEO WHERE BF.GEO_ID = GEO.GEO_ID tablespace BILLING_FACT_S PCTFREE 5 PARALLEL 4 LOCAL NOLOGGING;

You can create locally partitioned indexes on bitmap join indexes, as noted in the previous example with the LOCAL keyword. You can also create bitmap join indexes between the fact table and multiple dimension tables. For example,

CREATE BITMAP INDEX BILLING_FACT_BJIX02 ON BILLING_FACT (GEO.GEO_ID, TM.YYYYMMDD_DT) FROM BILLING_FACT ...