Generate Explain Plans
The explain plan displays the steps a database uses to execute an SQL statement. The results of the plan display the order a database uses to search/join tables, the types of access used (indexed search or full table scan), and the names of indexes used. If you are using cost-based optimization, this statement also determines the cost of executing the statement. You can use explain plans to help tune SQL to improve performance.
Toad automatically generates an estimated explain plan when you select the Explain Plan tab in the Results pane.
Tip: If you occasionally closed the Results pane, you can restore it by selecting Editor | Windows | Script Results.
To generate an estimated explain plan
- Click
in the Editor toolbar.
The following is a list of abbreviations used in DB2 Explain Plan:
| Value | Description |
|---|---|
|
DELETE |
Delete |
|
EISCAN |
Extended Index Scan |
|
FETCH |
Fetch |
|
FILTER |
Filter rows |
|
GENROW |
Generate Row |
|
GRPBY |
Group By |
|
HSJOIN |
Hash Join |
|
INSERT |
Insert |
|
IXAND |
Dynamic Bitmap Index ANDing |
|
IXSCAN |
Relational index scan |
|
MSJOIN |
Merge Scan Join |
|
NLJOIN |
Nested loop Join |
|
REBAL |
Rebalance rows between SMP subagents |
|
RETURN |
Result |
|
RIDSCN |
Row Identifier (RID) Scan |
|
RPD |
Remote PushDown |
|
SHIP |
Ship query to remote system |
|
SORT |
Sort |
|
TBFUNC |
In-stream table function operator |
|
TBSCAN |
Table Scan |
|
TEMP |
Temporary Table Construction |
|
TQ |
Table Queue |
|
UNION |
Union |
|
UNIQUE |
Duplicate Elimination |
|
UPDATE |
Update |
|
XISCAN |
Index scan over XML data |
|
XSCAN |
XML document navigation scan |
|
XANDOR |
Index ANDing and ORing over XML data |
|
ZZJOIN |
Zigzag join |