Browse DevX
Sign up for e-mail newsletters from DevX


DB2 Explains Itself: A Roadmap to Faster Query Runtime-3 : Page 3




Building the Right Environment to Support AI, Machine Learning and Deep Learning

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.

That covers the basic operators. There's a lot more information than just the node types and estimated cost. You can find it in IBM's "Description of db2expln and dynexpln Output."

Comment and Contribute






(Maximum characters: 1200). You have 1200 characters left.



Thanks for your registration, follow us on our social networks to keep up-to-date