8i | 9i | 10g | 11g | 12c | 13c | 18c | 19c | 21c | 23c | Misc | PL/SQL | SQL | RAC | WebLogic | Linux

Home » Articles » 9i » Here

Bitmap Join Indexes

In Oracle 8i performance improvements were made using materialized views to store the resulting rows of queries. The benefits of this mechanism are still relevant, but a certain subset of the queries used in a data warehouse may benefit from the use of Bitmap Join Indexes.

How It Works

In a Bitmap Index, each distinct value for the specified column is associated with a bitmap where each bit represents a row in the table. A '1' means that row contains that value, a '0' means it doesn't.

Bitmap Join Indexes extend this concept such that the index contains the data to support the join query, allowing the query to retrieve the data from the index rather than referencing the join tables. Since the information is compressed into a bitmap, the size of the resulting structure is significantly smaller than the corresponding materialized view.

Creation

The index is created with reference to the columns in the joined tables that will be used to support the query. In the following example an index is created where the SALES table is joined to the CUSTOMERS table.

CREATE BITMAP INDEX cust_sales_bji
ON    sales(customers.state)
FROM  sales, customers
WHERE sales.cust_id = customers.cust_id;

Since the CUSTOMERS.STATE column is referenced in the ON clause of the index, queries on the SALES table that join to the CUSTOMERS table to retrieve the STATE column can do so without referencing the CUSTOMERS table. Instead the data is read from the bitmap join index.

SELECT SUM(sales.dollar_amount)
FROM   sales,
       customer
WHERE  sales.cust_id  = customer.cust_id
AND    customer.state = 'California';

When dealing with large datasets, this reduction in processing can be substantial.

Restrictions

Bitmap Join Indexes have the following restrictions:

For more information see:

Hope this helps. Regards Tim...

Back to the Top.