Database Reference
In-Depth Information
because of lack of space, and nobody notices, any subsequent queries using
the index, as instructed to do so by the Optimizer, will simply not find table
rows not rebuilt into the index.
ALTER INDEX RELEASES_CD REBUILD COMPRESS ONLINE;
In fact, to rebuild an index, with all defaults, simply execute the follow-
ing command. The ONLINE option is a good idea in an active environ-
ment but not a syntactical requirement.
ALTER INDEX RELEASES_CD REBUILD ONLINE;
Next, we want to change the index on CD and SONG to a unique
index. An index cannot be altered from nonunique to unique using the
ALTER INDEX command. We must drop and re-create the existing index
in order to change the index to a unique index. The new index is also cre-
ated as a compressed index.
DROP INDEX RELEASES_CD_SONG;
CREATE UNIQUE INDEX RELEASES_CD_SONG
ON RELEASESIN2001 (CD, SONG) COMPRESS;
Incidentally, compression can be instituted using the ALTER INDEX
command, so we compress the index using the ALTER INDEX command
as shown in the following command:
ALTER INDEX RELEASES_CD REBUILD ONLINE COMPRESS;
Finally, rename the index on CD, ARTIST, and SONG.
ALTER INDEX RELEASES_CD_ARTIST_SONG RENAME TO RELEASES_3COLS;
21.1.5
More Indexing Refinements
Here are a few more points you should know about using indexes:
Primary, Foreign, and Unique Keys
. Primary and unique key con-
straints have indexes created automatically by Oracle Database. It is
recommended to create indexes for all foreign key constraints.
