Database Reference
In-Depth Information
Uniqueness
. Indexes can be unique or nonunique. Primary key con-
straints and unique constraints use unique indexes. Other indexed
columns, such as names or countries, sometimes need unique indexes
and sometime need nonunique indexes.
Composites
. A composite index is made up of more than one col-
umn in a table.
Compression
. Applies to BTree indexes and not bitmap indexes
where duplicated prefix values are removed. Compression speeds up
data retrieval but can slow down table changes.
Reverse keys
. Bytes for all columns in the index are reversed without
changing the column order. Reverse keys can help performance in
clustered server environments (Oracle Real Application Clusters, for-
merly Oracle Parallel Server) by ensuring that changes to similar key
values will be better physically spread. Reverse key indexing can apply
to rows inserted into OLTP tables using sequence integer generators,
where each number is very close to the previous number. Inserting
groups of rows with similar sequence numbers can cause some con-
tention because sequential values might be inserted into the same
block at the same time.
Null values
. If all of the indexed columns in a row contain null val-
ues, rows are not included in an index.
Sorting
that the
index being built is based on data that is already in the correct sorted
order. This can save a great deal of time when creating an index, but
will fail if the data is not actually in the order needed by the index.
This assumes that data space is physically ordered in the desired man-
ner, and the index will copy the physical order of the data space.
. The NOSORT clause tells Oracle Database 10
g
You are ready to begin creating some indexes.
21.1.3
Creating Indexes
Figure 21.3 shows a syntax diagram detailing the CREATE INDEX command.
Let's start by creating a table called RELEASESIN2001.
CREATE TABLE RELEASESIN2001 (CD,ARTIST,COUNTRY,SONG,RELEASED)
AS SELECT CD.TITLE AS "CD", A.NAME AS "ARTIST"
, A.COUNTRY AS "COUNTRY", S.TITLE AS "SONG"
