Altering the Access Structures

This technique is not tied to a specific feature. It is 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 to 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?