Database Reference
In-Depth Information
Continuing with the example in Figure 21.1, here is a query on the
CUSTOMER table:
SELECT VOCATION FROM CUSTOMER WHERE NAME = 'Ned';
Because the WHERE clause contains the indexed column (NAME), the
Optimizer should opt to use the index. Oracle Database 10
searches the
index for the value “Ned”, and then uses the ROWID as an address pointer
to read the exact row in the table. The value of the VOCATION column is
retrieved (“Pet Store Owner”) and returned as the result of the query.
A large table search on a smaller index uses the pointer (ROWID) found
in the index to pinpoint the row physical location in the table. This is very
much faster than physically scanning the entire table.
When a large table is not searched with an index, then a full table scan is
executed. A full table scan executed on a large table, retrieving a small num-
ber of rows (perhaps even retrieving a single row), is an extremely inefficient
process.
g
Note:
Although the intent of adding an index to a table is to improve per-
formance, it is sometimes more efficient to allow a full table scan when que-
rying small tables. The Optimizer will often assess a full table scan on small
tables as being more efficient than reading both index and data spaces, espe-
cially when a table is physically small enough to occupy a single data block.
Many factors are important to consider when creating and using
indexes. This shows you that simply adding an index may not necessarily
improve performance but usually does:
Too many indexes per table can improve read access and degrade the
efficiency of data changes.
Too many table columns in an index can make the Optimizer con-
sider the index less efficient than reading the entire table.
Integers, such as a social security number, are more efficient to index
than items such as dates or variable data like a topic title.
Different types of indexes have specific applications. The default
index type is a BTree index, the most commonly used index type.
