Oracle9i is the latest release of the leading database for data warehousing. Oracle leads in data warehousing because it satisfies the core requirements of the area: performance, scalability, and manageability. Oracle7 (Release 7.3), Oracle8, and Oracle8i each introduced significant capabilities to meet these core requirements. Oracle9i extends Oracle's leadership in data warehousing performance by introducing Bitmap Join Indexes.

A 'join index' is an index structure which spans multiple tables and improves the performance of joins of those tables. With materialized views, Oracle8i already provides a broad mechanism for improving join performance. Bitmap join indexes further improve performance for a specific class of join-queries. Bitmap join indexes can be particularly useful for star queries, and in some cases, bitmap join indexes can improve query performance by a factor of 30. Due to their space-efficient compressed storage, bitmap join indexes also take up little disk space.

Bitmap join indexes are best understood by examining a simple example. Suppose that a data warehouse contains a star schema with a fact table named Sales and a dimension table named Customer which holds each customer's home state. A bitmap join index can be created which indexes Sales by customer home states. Here is the SQL for the index:

CREATE BITMAP INDEX cust_sales_bji
ON Sales(Customer.state)
FROM Sales, Customer
WHERE Sales.cust_id = Customer.cust_id;

The bitmap join index above could be used to evaluate the following query. In this query, the CUSTOMER table will not even be accessed; the query is executed using only the bitmap join index and the sales table.

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

If the CUSTOMER table is a large dimension table (and customer-based dimension tables can reach tens of millions of records), then the bitmap join index can vastly improve performance by not requiring any access to the CUSTOMER table. In addition, bitmap join indexes can eliminate some of the key iteration and bitmap merge work which is often present in star queries with bitmap indexes on the fact table.

Below we present a test case showing the performance gains possible with bitmap join indexes. In our test case, we use data for a hypothetical manufacturer which tracks its orders of parts from various suppliers. We create a three-table bitmap join index with the fact table Partsupp and the dimension tables Parts and Suppliers. The columns used are Parts(Type) and Supplier(Nationkey). The table cardinalities are :

Supplier 6,000,000 rows
Parts 300,000 rows
Partsupp 24,000,000 rows

The column cardinalities are:
Parts(Type) : 150 distinct values Supplier(Nationkey) : 25 distinct values

To create a bitmap join index for these tables, we issue the following SQL:

CREATE BITMAP INDEX Partsupp_Parts_Suppliers
ON Partsupp( Parts.Type, Supplier.Nationkey)
FROM Partsupp, Parts, Supplier
WHERE Partsupp.Suppkey = Supplier.Suppkey AND
Partsupp.Partkey = Parts.Partkey;

Below we show a query which can be satisfied by the new index with no need to access either the Parts or Supplier tables:

SELECT COUNT (DISTINCT ps.Suppkey),
AVG(ps.Supplycost), MAX(ps.Supplycost),MIN(ps.Supplycost)
FROM Partsupp ps, Supplier s, Parts p
WHERE ps.suppkey = s.suppkey AND ps.Partkey=p.Partkey AND
s.Nationkey= 24 AND p.Type='medium burnished steel';

The query above returned 6475 rows from Partsupp. Oracle9i used a Bitmap Join Index access path to complete the query, while Oracle8i's best results came with a hash join of all three tables. The timings are shown below. Note that the 7 seconds time for Oracle9i was the result when reading all blocks from disk; with all blocks cached, the query finished in 2 seconds.

	Oracle8i	Oracle9i	Difference	Speedup Factor
Query Response Time	01:02	00:07	00:55	8.8x

Oracle9i introduces many significant data warehousing features. The results above show that the new bitmap join indexes can dramatically enhance data warehousing performance.

More Info

	Oracle9i for E-Business: Business Intelligence - Technical White Paper
	Oracle9i Data Warehousing Guide Release 1 (9.0.1) - Ch. 6: Indexes
	Performance and Scalability in DSS Environment with Oracle9i - Technical White Paper

Oracle9i Database Daily Features

Archives