Databases Reference
In-Depth Information
It's okay to have multiple indexes per table. However, the more indexes you place on a table, the
slower DML statements will run (as Oracle has more and more indexes to maintain when the table
column values change). Don't fall into the trap of randomly adding indexes to a table until you stumble
upon the right combination of indexed columns. Rather, verify the performance of an index before you
create it in a production environment. (See Chapter 7 for details on validating performance benefits.)
Note
You can have a column appear in multiple indexes on the same table. However, Oracle doesn't allow
multiple indexes in one table on the exact same combination of columns.
Indexing Guidelines
Oracle indexes provide efficient access to large data sets. Deciding on using an index involves
determining whether the improvement in performance
SELECT
statements is worth the cost of space
consumed and overhead when the table is updated. Table 1-2 summarizes the guidelines for efficiently
using indexes.
Table 1-2. Guidelines 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
