Index entries are stored in a
B-tree structure so that traversing the index to find the key value of the row uses very few I/O
operations. An index may serve a dual purpose in the case of a unique index: Not only will it
speed the search for the row, but it enforces a unique or primary key constraint on the indexed
column. Entries within an index are automatically updated whenever the contents of a table row
are inserted, updated, or deleted. When a table is dropped, all indexes created on the table are
also automatically dropped.
Several types of indexes are available in Oracle, each suitable for a particular type of table,
access method, or application environment. We will present the highlights and features of the
most common index types in the following subsections.
Unique Indexes
A unique index is the most common form of B-tree index. It is often used to enforce the primary
key constraint of a table. Unique indexes ensure that duplicate values will not exist in the column
or columns being indexed. A unique index may be created on a column in the EMPLOYEE table
for the Social Security Number because there should not be any duplicates in this column.
However, some employees may not have a Social Security Number, so this column would
contain a NULL value.
Non-Unique Indexes
A non-unique index helps speed access to a table without enforcing uniqueness. For example, we
can create a non-unique index on the Last_Name column of the EMPLOYEE table to speed up our
searches by last name, but we would certainly have many duplicates for any given last name.
Pages:
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82