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.