Databases Reference
In-Depth Information
efficient for SQL statements that use multiple
AND
or
OR
join operators in the
WHERE
clause (which is
typical in a data warehouse environment).
You should not use bitmap indexes in OLTP databases with high
INSERT
/
UPDATE
/
DELETE
activities.
This is because the structure of the bitmap index results in many locked rows during singular DML
operations (which results in locking problems for high-transaction OLTP systems).
A bitmap index is created using the keyword
BITMAP
. For completeness, we also show the table
creation script upon which the bitmap index is built.
create table f_sales(
sales_amt number
,d_date_id number
,d_product_id number
,d_customer_id number);
create bitmap index f_sales_fk1
on f_sales(d_date_id);
Note
Bitmap indexes and bitmap join indexes are available only with the Oracle Enterprise Edition of the
database.
Bitmap Join
A bitmap join index stores the results of a join between two tables in an index. These indexes are
beneficial because they avoid joining tables to retrieve results. Bitmap join indexes are appropriate in
situations where you're joining two tables using the foreign-key column(s) in one table that relate to
primary-key column(s) in another table.
Bitmap join indexes are usually suitable for data warehouse environments that have tables
periodically batch loaded and then are not updated. When updating tables that have bitmap join
indexes, this potentially results in several rows being locked. Therefore, this type of index is not suitable
for an OLTP database. A bitmap join index is specified with the keyword
BITMAP
and a join condition
must be provided. The following is an example (for completeness, we also show the join table creation
statement):
create table d_customers
(d_customer_id number primary key
,cust_name varchar2(30));
create bitmap index f_sales_bmj_idx1
on f_sales(d_customers.cust_name)
from f_sales, d_customers
where f_sales.d_customer_id = d_customers.d_customer_id;
