Database Reference
In-Depth Information
Figure 21.5
Using a Function
Based Index.
The user must have the following:
The QUERY_REWRITE system privilege.
Execute privileges on any user-defined functions.
Oracle Database configuration parameters must be set as follows:
QUERY_REWRITE_ENABLED = TRUE.
QUERY REWRITE_INTEGRITY = TRUSTED.
Now let's try a bitmap join index. The previous query demonstrating a
function-based index joined the MUSICCD table and the SALES fact
table. The MUSICCD table in this case could be considered a dimension of
the SALES fact table. Thus a bitmap index would be created on the SALES
table MUSICCD_ID column and joined to the MUSICCD_ID primary
key column on the MUSICCD facts table.
CREATE BITMAP INDEX XAKBJ_SALES_2
ON SALES (S.MUSICCD_ID)
FROM MUSICCD CD, SALES S
WHERE S.MUSICCD_ID = CD.MUSICCD_ID;
