Databases Reference
In-Depth Information
Now, we can create a bitmap join index on the join between the tables
CUSTOMERS
and
SALES
. To do so, we will use the following statements:
ALTER TABLE CUSTOMERS ENABLE VALIDATE CONSTRAINT CUSTOMERS_PK;
CREATE BITMAP INDEX BJI_CUST_SALES
ON SALES(CUSTOMERS.CUST_POSTAL_CODE)
FROM SALES, CUSTOMERS
WHERE SALES.CUST_ID = CUSTOMERS.CUST_ID LOCAL;
And we can execute the same query again; we will see the results displayed in the
next screenshot:
We will read less data, accessing only the
SALES
segment and the bitmap join index that
we just created.
There are some clarifications needed:
F
We have enabled and validated the primary key constraint on the
CUSTOMERS
table,
because we can create a bitmap join index only on a key field (dimension) on which
there is a unique constraint.
F
We have used the
LOCAL
keyword when we built the bitmap join index because the
SALES
table is partitioned, so only
LOCAL
(to the partition)
BITMAP INDEXES
can
be built on it.