Databases Reference
In-Depth Information
also reduces locking issues when updating and
inserting into child tables.
Carefully select and test indexes on small tables
(small being less than a few thousand rows).
Even on small tables, indexes can sometimes
perform better than full table scans.
Use the correct type of index.
Correct index usage maximizes performance. See
Table 1-1 for more details.
Use the basic B-tree index type if you don't have a
verifiable performance gain from using a different
index type.
B-tree indexes are suitable for most applications
where you have high cardinality column values.
Consider using bitmap indexes in data warehouse
environments.
These indexes are ideal for low cardinality
columns where the values aren't updated often.
Bitmap indexes work well on foreign key columns
on Star schema fact tables where you often run
queries that use
AND
and
OR
join conditions.
Consider using a separate tablespace for indexes
(separate from tables).
Table and index data may have different storage
and/or backup and recovery requirements. Using
separate tablespaces lets you manage indexes
separately from tables.
Let the index inherit its storage properties from the
tablespace.
This makes it easier to manage and maintain
index storage.
Use consistent naming standards.
This makes maintenance and troubleshooting
easier.
Don't rebuild indexes unless you have a solid
reason to do so.
Rebuilding indexes is generally unnecessary
unless an index is corrupt or you want to move an
index to different tablespace.
Monitor your indexes, and drop indexes that aren't
used.
Doing this frees up physical space and improves
the performance of data manipulation language
(DML) statements.
Before dropping an index, consider marking it as
unusable or invisible.
This allows you to better determine if there are
any performance issues before you drop the
index. These options let you rebuild or re-enable
the index without requiring the data definition
language (DDL) creation statement.
Refer to these guidelines as you create and manage indexes in your databases. These
recommendations are intended to help you correctly use index technology.
