Databases Reference
In-Depth Information
or
SET CURRENT EXPLAIN MODE EXPLAIN
The former generates Explain data while statements are executing, while the latter
generates Explain data for incoming statements, but otherwise instructs the database
not to execute the statements. As a result, after plan selection the query execution plan
information is written to the Explain database control tables and processing for the
statement stops (without executable code generation or execution).
For Oracle a similar process is enabled using the AUTOTRACE feature:
SET AUTOTRACE ON
Oracle SET AUTOTRACE ON is similar to DB2's SET CURRENT EXPLAIN
MODE YES.
Following plan capture in DB2 the db2exfmt command can be used to format the
Explain data to text, generating text output with a tree structure similar to the structure
shown through the graphical interfaces of most products. Here's an example for the fol-
lowing query:
SELECT year(dateofsale), avg(sales), color
FROM LIGHT.MyTable
WHERE REGION = 'NY'
GROUP BY (COLOR, YEAR(DATEOFSALE))
The Explain output for this query is shown in Figure 11.4. You'll notice the use of
keywords to describe the operators in the plan. They are, for the most part, fairly intuitive.
For example, GRPBY is used to indicate a group-by, TBSCAN is used for table scan, etc.
The number appearing in brackets is an identifier for each operator, and the detailed
report produced by Explain provides more information for each operator, which is num-
bered accordingly. The two numbers appearing below each operator are the total cost and
the input/output (I/O) cost, respectively.
There are some obvious differences between the text-based output of the two prod-
ucts. The DB2 scheme yields a tree structure very similar to the tree structure used in
the graphical interfaces of most products. Oracle's output is more tabular, using a nest
next structure to show the hierarchical relationships. The DB2 presentation is easier to
follow, provided the access path is narrow enough to fit within a text-based editor (usu-
ally 80-120 characters wide). However, for very wide plans the Oracle format stays
compact while the DB2 format wraps and can be more difficult to view in text. Clearly,
each format has its benefits.
