Guidelines for Creating Indexes |
 Previous |
 Next |
You can create indexes on columns to speed up queries. Indexes provide faster access to data for operations that return a small portion of the rows of in a table.
You can create an index on any column; however, if the column is not used in any of these situations, creating an index on the column does not increase performance, and the index takes up resources unnecessarily.
This section contains the following topics:
Index the Correct Tables and Columns
Use the following guidelines to determine when to create an index on a table or column:
-
Create an index on the columns that are used for joins to improve join performance.
-
You might want to create an index on a foreign key. See "Foreign Key Constraint" for more information.
-
Small tables do not require indexes. However, if a query is taking too long, then the table might have grown.
Columns with one or more of the following characteristics are good candidates for indexing:
-
Values in the column are unique, or there are few duplicate values.
-
There is a wide range of values.
-
The column contains many nulls, but queries often select all rows that have a value.
Columns that contain many null values are less suitable for indexing if you do not search on the non-null values.
Limit the Number of Indexes for Each Table
The more indexes, the more overhead is incurred as the table is altered. When rows are inserted or deleted, all indexes on the table must be updated. When a column is updated, all indexes on the column must be updated.
You must weigh the performance benefit of indexes for queries against the performance overhead of updates. For example, if a table is primarily read-only, you might use more indexes; but, if a table is heavily updated, you might use fewer indexes.
Drop Indexes That Are No Longer Required
-
It does not speed up queries. The table might be very small, or there might be many rows in the table but very few index entries.
-
The queries in your applications do not use the index.
You cannot drop an index that was created through a constraint. You must drop the constraint and then the index is dropped also.
If you drop a table, then all associated indexes are dropped. To drop an index, the index must be contained in your schema or you must have the DROP ANY INDEX system privilege.