Oracle SQL: the Essential Reference

8.4. Improving Query Performance

If your query performance is unsatisfactory, remember that methods are available to help you improve it. You may want to:

• Modify the database structure

• Gather up-to-date statistics

• Rewrite the query

• Use optimizer hints to control the execution plan

8.4.1. Modifying Database Structure

Database structure modifications usually involve the creation of indexes. It's important to have the proper indexes to support your queries, but indexes aren't always the answer to poor performance. Other structural changes include separating table data and index data onto separate disks, spreading data over multiple disks, partitioning the data, and clustering data. However, these changes aren't likely to improve performance dramatically if you have a poorly written query to begin with.

8.4.2. Gathering Statistics

If the cost-based optimizer is in use, then table and index statistics play a large role in determining the execution plan that Oracle will use for any given statement. It's important that there be a set of statistics that results in the generation of good execution plans. In theory, up-to-date statistics that reflect the data accurately would always result in the best execution plans. In practice, occasional performance drops have been experienced after analyzing tables. Oracle8i actually includes a feature that allows the import of an arbitrary set of statistics to serve as the basis for optimizer-generated execution plans.

8.4.3. Rewriting Queries ...