Databases Reference
In-Depth Information
Design Decision 3: Should I use a dense or sparse index?
When rows are small
relative to page size, sparse indexes are favored. A dense index will have many
more entries and will often require one more level of indexing to access data than
that for a sparse index. On the other hand, dense indexes can be effectively
merged to make composite indexes efficient for multipoint queries. When rows
are large relative to page size, a sparse index is rarely more efficient than a dense
index. Another advantage of a dense index is that it can often become a covering
index for certain queries.
Design Decision 4: When can I use a covering index?
Sometimes a composite
index can be used as a covering index for certain queries that can be satisfied by
only searching the index. For example, an index on an attribute's feature, make, and
model in that order could be used to completely satisfy a query:
SELECT make, model, vin, year
FROM carOnLot
WHERE feature = 'catalytic converter';
Design Decision 5: Should I create a clustered index?
The basic rules for clustered
indexes are:
1.
Only one clustered index can be created for a given table, otherwise there would
be major conflicts among the rows satisfying multiple queries using different
indexes.
2.
If an index can be used as a covering index, then no clustering is needed because
the query can be satisfied by accessing the index only.
3.
Range queries, multipoint queries, and single-point queries on nonprimary key
values may all benefit from clustering. Each type of query involves accessing
multiple rows, and any clustering would improve performance. If you have sev-
eral choices of which rows to cluster, a tradeoff analysis may be needed to deter-
mine which choice optimizes performance (see Chapter 2).
Design Decision 6: Is an index still preferred when updates are taken into
account? What are the tradeoffs between queries and updates for each index cho-
sen?
Once the indexes are chosen to improve performance for known queries, con-
sider all the updates (inserts, deletes, updates) on the target tables. Looking at the
tradeoffs from a cost/benefit perspective, consider the I/O time for all transactions
involving queries and updates of each target table for a given index, taking into
account the frequency of each query and update over a fixed time period:
