Bitmap Join Indexes

Bitmap Join Indexes

Oracle8i supported bitmap indexes on a single table. 9i adds bitmap join indexes, built on two tables, to optimize access for a specific join condition:

CREATE BITMAP INDEX my_bit_join_ix
ON emp_table (e.job)
FROM emp_table e, dept_table d
WHERE e.deptno = d.deptno ;

Restrictions:

• Can not reference an IOT, temporary table, or a self-join
• Only one table can be updated concurrently by different transactions
• Parallel DML is only supported on the fact table, and the columns in the index must all be columns of the dimension tables
• Join must be on primary key or unique-constraint key, and, if the key is composite, the entire key must be used