Databases Reference
In-Depth Information
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 BF, GEOGRAPHY_DIMENSION GEO, TIME_DIMENSION TM
WHERE BF.GEO_ID = GEO.GEO_ID
AND BF.YYYYMMDD_DT = TM.YYYYMMMDD_DT
tablespace BILLING_FACT_S
PCTFREE 5
PARALLEL 4
LOCAL
NOLOGGING;
If you have a snowflake schema, which is an extension of a star schema with child entities off of the
dimension tables, you can also create a bitmap join index off of the dimension tables in a snowflake
schema. The syntax is, in essence, identical.
Reporting on Bitmap Indexes
It is fairly simple to get information on bitmap indexes from the data dictionary. See the following query
from the
USER_INDEXES
view in order to get information on both bitmap and bitmap join indexes:
SELECT index_name, index_type, join_index FROM dba_indexes
WHERE index_type = 'BITMAP';
INDEX_NAME INDEX_TYPE JOI
------------------------------ --------------------------- ---
BILLING_FACT_BIXFK102 BITMAP NO
BR_FACT_BJIX002 BITMAP YES
If you want to get the specific join information on any bitmap join indexes you have in your
database, you can get this information from the
USER_JOIN_IND_COLUMNS
data dictionary view. This view
