Database Reference
In-Depth Information
Example 8:
The following statements a) renames the
Hall
table to
Dormitory
and b)
deletes all tuples of the
Student
table.
11.8 Working with Indexes
An index is a database object that is used to speed up the retrieval of tuples from base
relations. The index is independent of the table it indexes. It stores address pointers
to tuples of the base relation. Once created, indexes are automatically maintained by
the DBMS, and used to service certain user access requests as required. The software
engineer's (or DBA's) only responsibility is to create them.
An index may be created whenever any of the following circumstances holds:
•
A column or combination of columns is frequently used in system
query conditions (e.g. join conditions).
•
A column contains a wide range of values.
•
A column contains a large number of null values.
•
The table is large and most queries on it are expected to retrieve
less than 2 % - 4% of the tuples.
•
A column or combination of columns is frequently used in the
Order-By
clause of queries.
An index is not necessary when any of the following circumstances holds:
•
The table is small.
•
Most queries are expected to retrieve more than 2% - 4% of the
tuples.
•
The table is frequently updated (there are exceptions to this rule).
•
Columns are not often used in database queries.
An experienced software engineer or DBA can reasonably determine what indexes
are to be created in a database, as this could seriously affect the performance of the
database. When queries are executed, the DBMS first checks to see whether there are
indexes that will facilitate efficient access of the requested data. If it finds such indexes, it
uses them; otherwise, it creates temporary indexes required to service the queries.
Figure
11-14
shows the full syntax of the
Create-Index
statement, an abridged
version of which is provided in Figure
11-16
. Figure
11-15
provides the full syntax for the
Alter-Index
statement.
