Database Reference
In-Depth Information
BTree indexes are often the only index type used in anything but a
data warehouse.
The Optimizer looks at the SQL code in the WHERE, ORDER BY,
and GROUP BY clauses when deciding whether to use an index. The
WHERE clause is usually the most important area to tune for index
use because the WHERE clause potentially filters out much
unwanted information before and during disk I/O activity. The
ORDER BY clause, on the other hand, operates on the results of a
query, after disk I/O has been completed. Disk I/O is often the most
expensive phase of data retrieval from a database.
Do not always create indexes. Small tables can often be read faster
without indexes using full table scans.
Do not index for the sake of indexing.
Do not overindex.
Do not always include all columns in a composite index. A composite
index is a multiple-column index. The recommended maximum
number of columns in a composite index is three columns. Including
more columns could make the index so large as to be no faster than
scanning the whole table.
Next we discover what types of indexes there are, plus how and where
those different types of indexes can be used.
21.1.2
Types of Indexes
Oracle Database 10
supports many different types of indexes. You should
be aware of all these index types and their most appropriate or common
applications. As already stated, the most commonly used indexed structure
is a BTree index.
g
BTree Index
. BTree stands for binary tree. This form of index stores
dividing point data at the top and middle layers (root and branch
nodes) and stores the actual values of the indexed column(s) in the
bottom layer (leaf nodes) of the index structure. The branch nodes
contain pointers to the lower-level branch or leaf node. Leaf nodes
contain index column values plus a ROWID pointer to the table row.
Oracle Database 10
will attempt to balance the branch and leaf
nodes so that each branch contains approximately the same number
g
