DB2 Explains Itself: A Roadmap to Faster Query Runtime-3 : Page 3
by Greg Nash, DB2 Pro
Nov 1, 2001
Page 3 of 4
The Plan Explained
Figure 3. Examining the Nodes: This larger graph shows some nodes feeding multiple higher nodes (using temporary tables).
Whichever explain tool you use, you will have an estimate of the cost of the query in "timerons". A "timeron" is an abstract unit of measure. While it doesn't directly equate to any actual elapsed time, it does give a rough relative estimate of the resources (cost) required by a database manager to execute an access plan. You can try different variations of a query, different optimization levels or other changes, and look for a lower cost to indicate a faster result. Working from the bottom up, examining the entire plan may help you find specific inefficiencies. The explain lists and graphs show how the required data is located, collated, and prepared for return. Note that the graph isn't always a tree - it takes sensible shortcuts if it saves performing the same operation twice. At each node, you can examine the arguments to the operator, the estimated cost of the node, and the cumulative cost of all operations up to this point.
Here are the main node types:
UPDATE, DELETE, INSERT: perform the operations named.
FETCH: retrieves data from a table, given a specific pointer to the record.
TBSCAN: A table scan is examination of an entire table. It's a bit like visiting every aisle in a supermarket and examining every item. It's necessary at times, but can be most inefficient when you want a small portion of a large table (or shop).
IXSCAN, EISCAN, RIDSCN: Index Scanning. Indexes are pointers to rows, sorted for fast access. It's like having a store directory, so you can go directly to the products you want. EISCAN operates with multiple ranges of values. RIDSCN retrieves the row pointers (Row IDs) from a set of index records.
FILTER: reduces a set of records to only those required
UNIQUE: removes duplicates
Joins - NLJOIN, MSJOIN, HSJOIN, UNION, IXAND: Join two record sets. The method chosen depends on the query and on the relationships between the tables involved. IXAND joins the results of index scans
GRPBY: Group By. Groups rows, and performs column function calculations on the groups.
TEMP: stores a set of results for re-use elsewhere in the query.