Database Reference
In-Depth Information
ON RELEASESIN2001 (COUNTRY);
Note:
Be very careful using bitmap indexes in place of BTree indexes.
We have just created five indexes on the RELEASESIN2001 table.
Note:
Every DML operation (INSERT, UPDATE, or DELETE) would
change the table and five indexes: six updates in total! Having so many
indexes on one table is not advisable with respect to performance. However,
for a data warehouse table it is fine, because changes to the tables are usually
done in batches periodically. You could possibly remove the indexes during
updates and then re-create the indexes afterward.
Now let's get a little more specialized and create a function-based index.
The following example creates a function-based index on the MUSIC
schema SALES data warehouse fact table.
CREATE INDEX XAKFB_SALES_1
ON SALES((SALE_PRICE-SHIPPING_COST)*SALE_QTY);
We could then query the SALES table and probably persuade the Opti-
mizer to access the index in the WHERE clause with a query something
like the following. The result is shown in Figure 21.5.
SELECT CD.TITLE "CD"
, SUM(S.SALE_PRICE-S.SHIPPING_COST) "Net Price"
, SUM(S.SALE_QTY) "Qty"
, SUM((SALE_PRICE-SHIPPING_COST)*SALE_QTY) "Revenue"
FROM MUSICCD CD JOIN SALES S USING (MUSICCD_ID)
WHERE ((SALE_PRICE-SHIPPING_COST)*SALE_QTY) > 10
GROUP BY CD.TITLE;
There are some points to note about function-based indexes. Some spe-
cific settings are required in Oracle Database to allow use of function-based
indexes.
Cost-based optimization is required.
