Non-Join Nodes in a QEP
Types of non-join nodes are as follows:
• Orig
• Projection-restriction
• Exchange
Orig Nodes
Orig nodes are nodes with no children. When reading a QEP, you should first find the orig nodes of the tree. Orig nodes are the most common type of QEP node.
Orig nodes describe a base table or secondary index being accessed from the query. This type of node displays the following:
• Table or index name
• Storage structure
• Total number of pages and tuples
For a description of each of these parts of the display, see
Information on a QEP.
Projection-Restriction Nodes
A projection-restriction (proj-rest) node describes the result of a projection and/or WHERE clause restriction applied to a subsidiary node. It defines how a subsidiary node is to key into the table and what qualification to use on the table. This type of node displays the following:
• A label identifying it as a proj-rest node
• Storage structure (which is usually heap)
• Total number of pages and tuples
• Query cost information
• Optionally, sort information (whether the data produced by this node is already sorted or requires sorting)
For a description of each of these parts of the display, see
Information on a QEP.
Proj-rest nodes are used to remove data irrelevant to the query from a table, so that the minimum amount of data is carried around during the execution of the query. Therefore, only those columns referenced in the target list and WHERE clause for a table are projected, and only those rows that satisfy the WHERE clause restrictions are retained for use higher in the plan.
All you see is the amount of disk I/O required to read the appropriate rows from the node below, and that amount depends on what storage structures were used and the number of pages accessed.
Exchange Nodes
Exchange nodes appear in parallel query plans. An exchange node results in one or more threads being spawned to execute the plan fragment beneath the node. It allows different portions of a complex query plan to execute concurrently, reducing the overall elapsed time taken to execute the query. This type of node displays:
• Estimated reduction in execution time due to the presence of the exchange node
• Count of child threads spawned to execute the plan fragment below the exchange node
• PC Join count, the number of join fragments performed by a partition compatible join
Examples of Non-join Nodes
Here are QEP examples that illustrate non-join nodes. The Sample Tables section describes the tables and indexes used in these examples.
Sample Tables
In these examples, the following two tables are used:
1. Table arel(col1, col2, col3):
Name: arel
Owner: supp60
Created: 26-oct-1998 08:50:00
Location: ii_database
Type: user table
Version: II2.5
Row width: 413
Number of rows: 156
Storage structure: hash
Duplicate Rows: allowed
Number of pages: 70
Overflow data pages: 6
Column Information:
Column Key
Name Type Length Nulls Defaults Seq
col1 integer 4 yes no 1
col2 integer 4 yes no
col3 varchar 400 yes no
Secondary indexes: aindex (col2) structure: isam
2. Table brel(col1,col2):
Name: brel
Owner: supp60
Created: 26-oct-1998 08:53:00
Location: ii_database
Type: user table
Version: II2.5
Row width: 10
Number of rows: 156
Storage structure: isam
Duplicate Rows: allowed
Number of pages: 3
Overflow data pages: 1
Column Information:
Column Key
Name Type Length Nulls Defaults Seq
col1 integer 4 yes n 1
col2 integer 4 yes no
Secondary indexes: none
Primary Key Lookup
This is an example of a simple primary key lookup. The QEP is shown below for the following SQL query:
SELECT col1, col2 FROM arel
WHERE col1 = 1234
ORDER BY col2;
QUERY PLAN 3,1, no timeout, of main query
Sort Keep dups
Pages 1 Tups 1
D1 C0
/
Proj-rest
Sorted(col1)
Pages 1 Tups 1
D1 C0
/
arel
Hashed(col1)
Pages 70 Tups 156
Reading this QEP diagram from bottom to top, Hashed(col1) means the row is being read through the index to return only those rows for which “col1 = 1234,” as opposed to Hashed(NU) where NU indicates that the key structure cannot be used and all rows are returned. The projection-restriction node selected the rows matching the where constraint and removed superfluous columns. The final sort node reflects the SORT clause on the query.
Select on a Non-Keyed Field
The following is an example of a select on a non-keyed field. The QEP is shown below for the following SQL query:
SELECT col1, col2 FROM arel
WHERE col3 = 'x'
ORDER BY col1;
QUERY PLAN 3,1, no timeout, of main query
Sort Keep dups
Pages 1 Tups 1
D9 C0
/
Proj-rest
Heap
Pages 1 Tups 1
D9 C0
/
arel
Hashed(NU)
Pages 70 Tups 156
In this example the Hashed(NU) implies that the table had to be scanned (that is, all 70 pages had to be visited). Without optimization statistics, the query optimizer uses a best guess approach (1% for equalities and 10% for non-equalities).
The query optimizer takes into account disk read-ahead or group reads when performing scans of tables—although 70 pages of data have to be read to scan arel, the estimated disk I/O value is only nine reads (D9) due to this effect. The query optimizer assumes a typical read-ahead of eight pages when performing scans, so here 70/8 reads generates an estimate of nine disk operations.