Aspects of Implementing and Maintaining Indexes

There are various aspects of implementing and maintaining indexes. The table below summarizes some to the guidelines and techniques for creating indexes.

Guideline	Reasoning
Create as many indexes as you need, but try to keep the number to a minimum. Add indexes judiciously. Test first to determine quantifiable performance gains.	Indexes increase performance, but also consume disk space and processing resources. Don’t add indexes unnecessarily.
The required performance of queries you execute against a table should form the basis of your indexing strategy.	Indexing columns used in SQL queries will help performance the most.
Consider using the SQL Tuning Advisor or the SQL Access Advisor for indexing recommendations.	These tools provide recommendations and a second set of "eyes" on your indexing decisions.
Create primary key constraints for all tables.	This will automatically create a B-tree index (if the columns in the primary key aren’t already indexed).
Create unique key constraints where appropriate.	This will automatically create a B-tree index (if the columns in the unique key aren’t already indexed).
Create indexes on foreign key columns.	Foreign key columns are usually included in the WHERE clause when joining tables and thus improve performance of SQL SELECT statements. Creating a B-tree index on foreign key columns also reduces locking issues when updating and inserting into child tables.
Carefully select and test indexes on small tables (small being fewer 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 8-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 in which you have high-cardinality column values.
Consider using bitmap indexes in data warehouse environments.	These indexes are ideal for low-cardinality columns in which the values aren’t updated often. Bitmap indexes work well on foreign key columns on star schema fact tables in which you often run queries that use AND and OR join conditions.
Consider using a separate tablespace for indexes (i.e., separate from tables).	Table and index data may have different storage or backup and recovery requirements, or both. 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 unusable or you want to move an index to different tablespace.
Monitor your indexes, and drop those that aren’t used.	Doing this frees up physical space and improves the performance of DML statements.
Before dropping an index, consider marking it unusable or invisible.	This allows you to better determine if there are anyperformance issues before you drop the index. These options let you rebuild or reenable the index without requiring the DDL creation statement.

These recommendations are intended to help you correctly use indexes as you create and manage indexes in your database environment.