You can use explain information to analyze the access plan that the optimizer has chosen based on the choices described in "Data Access Concepts and Optimization". For example, explain information may indicate that an index scan (see "Index Scan Concepts") was chosen by the optimizer. In addition, it can also allow you to determine the following:
As another example, the explain information could also help you understand how two tables are joined:
Although you can use explain for SELECT, SELECT INTO, UPDATE, INSERT, VALUES, VALUES INTO, and DELETE SQL statements, the primary use of explain is to observe the access paths for the SELECT parts of your statements.
To satisfy an SQL query, the database manager typically:
For a simple SQL query, such as:
SELECT DEPTNO, DEPTNAME FROM DEPARTMENT
the following, graphical representation of the steps performed could be displayed by Visual Explain:
Figure 51. Graphical Display of Explain Output
The following topics discuss the type of details you can view for objects and operators:
A single access plan may use one or more data objects to satisfy the SQL statement.
Object Statistics: The explain facility records facts about the object, such as:
A single access plan may perform several operations on the data to satisfy the SQL statement and provide results back to you. The SQL compiler determines the operations required; for example, a table scan, an index scan, a nested loop join, or a group-by. Details of many of these operators are provided in "Data Access Concepts and Optimization".
In addition to showing the various operators used in an access plan, explain information is also available for each operator as well as the cumulative effects of the access plan.
Estimated Cost Information: The following estimated, cumulative costs can be displayed for the operators. These costs are for the chosen access plan, up to and including the operator for which the information is captured.
Timerons are a made-up, relative unit of measure.
Operator Properties: The following information is recorded by the explain facility to describe the properties of each operator:
[ DB2 List of Books | Search the DB2 Books ]