Was this helpful?
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.
Last modified date: 01/30/2023