Database Reference
In-Depth Information
What this command has done is to create what is effectively a prejoined
index between the SALES and MUSICCD tables. The ON clause identifies
the SALES table as the fact table, including both fact and dimension tables
in the FROM clause, and the WHERE clause performs the join. Voilà! A
bitmap join index.
Now let's look into changing and dropping indexes.
21.1.4
Changing and Dropping Indexes
The indexes we created in the previous section were adequate, but they can
be improved. Many index improvements and alterations can be made using
the ALTER INDEX command, whose syntax is shown in Figure 21.6.
What about those improvements to our indexes created on the
RELEASESIN2001 table? Some of the indexes cannot be changed using
the ALTER INDEX command. Some index changes have to be made by
dropping and re-creating the index. The syntax for the DROP INDEX
command is very simple and is also shown in Figure 21.6.
Let's go ahead and change some of the indexes we created in the previ-
ous section. First, compress the index you created on the CD column. The
ONLINE option creates the index in temporary space, only replacing the
original index when the new index has completed rebuilding. This mini-
mizes potential disruption between building an index and DML or query
activity during the index rebuild. If, for example, an index build fails
Figure 21.6
ALTER INDEX
and DROP
INDEX Syntax.
