Database Reference
In-Depth Information
database schema is good and the configuration of the query optimizer is correctly performed, you usually have to
optimize a small number of SQL statements. Therefore, you want to avoid techniques impacting the SQL statements
for which the query optimizer automatically provides an efficient execution plan. Second, whenever you deal with
an application for which you have no control over the SQL statements (either because the code isn't available, like
in a packaged application, or because it generates SQL statements at runtime), you can't use techniques that require
changes to the code. In summary, more often than not, your choice is restricted.
The aim of this chapter isn't to describe how to find out what the best execution plan for a given SQL statement is,
for example, by explaining in which situation a specific access or join method should be used. This analysis is covered
in the chapters in Part 4. The purpose of this chapter is solely to describe the available SQL optimization techniques.
Each section that describes a SQL optimization technique is organized in the same way. A short introduction is
followed by a description of how the technique works and when you should use it. All sections end with a discussion
of some common pitfalls and fallacies.
Altering the Access Structures
This technique isn't tied to a specific feature. It's simply a fact that the response time of a SQL statement is strongly
dependent not only on how the processed data is stored but also on how the processed data can be accessed.
How It Works
The first thing you have to do while questioning the performance of a SQL statement is verify which access structures
are in place. Based on the information you find in the data dictionary, you should answer the following questions:
•
What is the organization type of the tables involved? Is it heap, index-organized, or external?
Or is the table stored in a cluster?
•
Are materialized views containing the needed data available?
•
What indexes exist on the tables, clusters, and materialized views? Which columns do the
indexes contain and in what order?
•
How are all these segments partitioned?
Next you have to assess whether the available access structures are adequate to efficiently process the SQL
statement you're optimizing. For example, during this analysis, you may discover that an additional index is necessary
to efficiently support the
WHERE
clause of the SQL statement. Let's say that you're investigating the performance of the
following query:
SELECT *
FROM emp
WHERE empno = 7788
Basically, the following execution plans can be considered by the query optimizer to execute it. While the first
execution plan performs a full table scan, the second one accesses the table through an index. Naturally, the second
can be considered only if the index exists:
----------------------------------
| Id | Operation | Name |
----------------------------------
| 0 | SELECT STATEMENT | |
| 1 | TABLE ACCESS FULL| EMP |
