Understanding Indexes
An index is a data structure that can greatly reduce the time it takes to find particular rows in the associated table.
A table object can have one or more indexes assigned to it. Figure 13.1 shows the syntax for creating an index on a table. Indexes can be created on one or more table columns. These columns, when relating to indexes, are known as key fields, or keys. A composite key uses more than one table column in a single index. Indexes are automatically created for the primary key constraint and can be unique—that is, have only one key value stored—or can have many keys with the same value. The more unique the key, the faster the access to the rows in the table.
Figure 13.1. SQL*Plus Help text about Create Index syntax.
Each row in a table has a unique identifier called ROWID. This ROWID is a pseudocolumn (false column) that contains the exact location of a row within the database. It also contains the following information: object ID, data file ID, block ID, and ROWID. In addition, ROWID is the fastest way to access a row in any version of Oracle. Any time the row is moved, exported, imported, and so on, this ROWID changes because the physical location of the row has changed. Indexes store ROWID along with the index key fields, and Oracle automatically updates all references to ROWID if and when a row is inserted or moved.
NOTE
Pseudocolumn— A column that can be selected with standard SQL syntax but does not occupy any space in the database. ROWID is a pseudocolumn that is really an exact physical location of that particular row.
NOTE
Using indexes greatly enhances the response time of most SQL statements. The rule of thumb here is if more than 20% of the rows of a table are going to be returned, you should consider not using indexes. The next sections cover the various indexing features of Oracle8i. Understanding the various available indexing options is important to choose the optimal indexing method for a given SQL statement.