12. Using the Query Optimizer : Types of Nodes in a QEP : Non-Join Nodes in a QEP : Exchange Nodes
 
Share this page                  
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.