Operator Details -- Fields and controls

Database Partitions

Save As

Print

Close

Cumulative costs

Cumulative properties

Input arguments

DELETE

FETCH

FILTER

GRPBY

HSJOIN

INSERT

IXSCAN

MSJOIN

NLJOIN

RIDSCN

SORT

TBSCAN

TEMP

TQUEUE

UNIQUE

UPDATE

Database Partitions

Click on this push button to display a list of database partitions that have been accessed thus far in the access plan. (This push button is displayed only for inter-partition parallelism or full parallelism (intra-partition and inter-partition)).

Cumulative Cost

The costs shown in the Operator Details window represent the estimated cumulative costs up to and including the point where the action represented by the operator is performed. These costs are estimated by the optimizer.

(If the operator type is RETURN, its cost is the estimated overall cost for the entire query.)

Note:

The cost is irrelevant if it is calculated using default statistics. For partitioned databases, all costs are for the node that uses the most resources.

The following costs are shown:

Total cost

The estimated cumulative cost of executing the current access plan (in timerons).

CPU cost

The estimated cumulative CPU cost (in number of instructions).

I/O cost

The estimated cumulative input/output (I/O) cost (in number of seeks and page transfers).

First row cost

The estimated cumulative effort (in timerons) required to produce the first row in the set of rows that results when the action represented by the operator is performed.

(This item is available in Full details mode only.)

Communication cost

The estimated cumulative communication cost (in number of IP (Internet Protocol) Frames).

First communication cost

The estimated cumulative communication cost (in number of IP Frames) required to produce the first row in the set of rows that results when the action represented by the operator is performed.

Cumulative Properties

The following properties are shown in the Full detail mode of the Operator Details window:

Tables

The set of tables that has been accessed thus far in the access plan.

Columns

The set of columns that has been accessed thus far in the access plan.

Order Columns

The columns on which this stream is ordered. They can be ascending (ASC), or descending (DESC). A value of -1 indicates that they are not ordered.

Predicates

The set of predicates applied (including an estimate of their selectivity).

Cardinality

The estimated number of rows to be returned.

Be sure to use the runstats command after making your table updates; otherwise, the table may appear to be empty to the optimizer. This problem is evident if the number of rows is zero (0). In this case, complete your table updates, rerun runstats, and recreate the explain snapshots for the table.

Total Buffer Pool Pages Used

The estimated number of pages in the buffer pool that will be required during processing of this operator and its inputs.

Partitioning map identifier

A partition number that maps a partitioning map index to database partitions in the nodegroup.

Input Arguments

DELETE

For the DELETE operator, the following input argument is shown:

Deleted table

The name of the table from which rows are to be deleted.

FETCH

For the FETCH operator, the following input arguments are shown:

Fetched table

The name of the table to be accessed.

Columns retrieved

The set of columns to be accessed from the table.

Sargable predicates

The set of predicates to be applied as sargable (that is, that can be resolved by the database manager by simple comparisons rather than by subqueries).

If the FETCH operator is associated with an index scan (IXSCAN) operator but is separated from it by other operators, re-apply the predicates of the index scan as residuals rather than sargables to ensure correct results.

Residual predicates

The set of additional predicates to be applied as residuals.

Direct fetch

The row-identifier column that is used to locate the row.

Prefetch

A flag that indicates the type of prefetching. Possible values are:

• Sequential prefetch is enabled.
• A positive number indicating the number of row identifiers (RIDs) for list prefetch.
• Prefetching is not enabled.

Maximum pages

The maximum number of pages that are expected to be read from disk. Possible values are:

• None - Prefetching is not enabled. Note: This is different from the prefetch argument, which indicates whether or not the optimizer expects a prefetch to be done. Run time is not influenced by the prefetch argument.
• All - All pages of the index or table are expected to be read. The decision about whether to do a prefetch is made at run time.
• A positive number - Indicates how many pages are expected to be read. The closer the number is to zero, the more the prefetch quantity will be reduced, or prefetch will not be done at all.

Lock intents

The lock modes that DB2 uses to access the data for the table, and optionally to access individual rows. Lock modes include share, exclusive, update or reuse, or next_key_share. They determine the level of concurrency that is possible. For the lock mode reuse, the lock mode for the original base table scan is reused. For more information, see the section on locking in the Administration Guide .

FILTER

For the FILTER operator, the following input argument is shown:

Applied predicates

The set of predicates to be applied to each row.

GRPBY

For the GRPBY operator, the following input arguments are shown:

Group by requirement

The columns on which groups are to be formed.

Group by columns

A flag that is true if there are any columns on which groups are to be formed.

If no GROUP BY columns are specified, the GRPBY operator will still be used if there are aggregation functions in the SELECT list, indicating that the entire table is treated as a single group when that aggregation is done.

One fetch

A flag that is true if only one row is needed as input to the aggregation, because the column function is either MIN(C) or MAX(C) and the input is ordered correctly on column C.

Number of comparison columns

The number of columns needed to determine group breaks.

Aggregation mode

The group by operation may be divided into multiple steps. The aggregation mode describes the level of aggregation performed at each step:

• Complete - Aggregation is done in a single pass.
• Partial - Aggregations are done on chunks of input rows.
• Intermediate - Partial aggregation results are processed to generate consolidated partial results.
• Final - Partial aggregation results are processed and any final calculations performed to generate the aggregation result.

HSJOIN

For the HSJOIN operator, the following input arguments are shown:

Early out

A flag that is true if only one inner row matching outer row is needed ("early out").

Outer join type

A flag indicating which type of outer join this NLJOIN operator is performing:

• None
• Left
• Right
• Full

Join predicates

The join predicates upon which the join is performed.

Bit filter

A flag that is true if a bit filter is used to enhance hash join performance.

INSERT

For the INSERT operator, the following input argument is shown:

Inserted table

The name of the table into which rows are to be inserted.

IXSCAN

For the IXSCAN operator, the following input arguments are shown:

Access path

The access path (index) identifier.

Scanned table

The quantifier of the table to be scanned.

Columns retrieved

The set of columns to be accessed.

Sargable predicates

The set of predicates to be applied as sargable (that is, that can be resolved by the database manager by simple comparisons rather than by subqueries).

Start predicates

The range-delimiting predicates that provide a starting value for an index search.

Stop predicates

The range-delimiting predicates that provide a stopping value for an index search.

Residual predicates

The set of additional predicates to be applied as residuals.

Prefetch

A flag indicating the type of prefetching. Possible values are:

• Sequential detection is enabled.
• A positive number indicating the number of row identifiers (RIDs) for list prefetch.
• Prefetching is not enabled.

Maximum pages

The maximum number of leaf pages of the index expected to be read from disk. Possible values are:

• None - Prefetching is not enabled. Note: This is different from the prefetch argument, which indicates whether or not the optimizer expects a prefetch to be done. Run time is not influenced by the prefetch argument.
• All - All pages of the index or table are expected to be read. The decision about whether to do a prefetch is made at run time.
• A positive number - Indicates how many pages are expected to be read. The closer this number is to zero, the more the prefetch quantity will be reduced, or prefetch will not be done at all.

Lock intents

The lock modes that DB2 uses to access the data for the table, and optionally to access individual rows. Lock modes include share, exclusive, update, or next_key_share. They determine the level of concurrency that is possible. (Displayed if the scan is over the base table.) For more information, see the section on locking in the Administration Guide .

Intra-partition scan type

Indicates if the index scan is performed in parallel by each subagent or if each subagent performs a private index scan.

• None - A private index scan.
• Local parallel - The index scan is performed in parallel by each subagent.

(This item is displayed only for intra-partition parallelism or full parallelism (intra-partition and inter-partition)).

Intra-partition scan unit

The scan granularity unit. That is, when the value of Intra-partition scan type is set to Local parallel, the assigned range is scan granularity number of scan units. For example, if scan granularity = 2, scan units = Row, a local parallel index scan will assign 2 Rows of data to each subagent, and as each subagent completes its 2 Rows, it will be assigned 2 more Rows.

• Row - Each subagent gets scan granularity rows at a time. For index scan, a row is an index key value.

(This item is displayed only for intra-partition parallelism or full parallelism (intra-partition and inter-partition)).

(This item is available in Full details mode only.)

Intra-partition scan granularity

The number of scan units that a subagent is assigned when the value of Intra-partition scan type is set to Local parallel. (This item is displayed only for intra-partition parallelism or full parallelism (intra-partition and inter-partition)).

(This item is available in Full details mode only.)

MSJOIN

For the MSJOIN operator, the following input arguments are shown:

Join predicates

The join predicates upon which the join is performed.

Outer order columns

The ordering columns of the outer stream.

Inner order columns

The ordering columns of the inner stream.

Residual predicates

The set of additional predicates to be applied as residuals.

Early out

A flag that is true if only one inner row matching outer row is needed ("early out").

Outer join type

A flag indicating which type of outer join this MSJOIN operator is performing:

• None
• Left
• Right
• Full

NLJOIN

For the NLJOIN operator, the following input arguments are shown:

Early out

A flag that is true if only one inner row matching outer row is needed ("early out").

Join predicates

The join predicates upon which the join is performed.

Max pages for IXSCAN

The maximum number of leaf pages of the index for the inner join, expected to be read from disk for all rows of the outer join. The closer this number is to zero, the more the prefetch quantity will be reduced.

Max pages for FETCH

The maximum number of pages of the table for the inner join, expected to be read from disk for all rows of the outer join. The closer this number is to zero, the more the prefetch quantity will be reduced.

Outer join type

A flag indicating which type of outer join this NLJOIN operator is performing:

• None
• Left
• Right
• Full

RIDSCN

For the RIDSCN operator, the following input argument is shown:

Estimated rows

The estimated number of rows that the RIDSCN will process.

SORT

For the SORT operator, the following input arguments are shown:

Order columns

An ordered list of columns that are to be sorted (that is, the sort keys).

Uniqueness

A flag that is true if duplicates are to be eliminated.

Estimated rows

The estimated number of rows that will be produced when the sort is finished.

Estimated row width

The width, in bytes, of a single row in the sort buffer.

Aggregation mode

The group by operation may be divided into multiple steps. The aggregation mode describes the level of aggregation performed at each step:

• Complete - Aggregation is done in a single pass.
• Partial - Aggregations are done on chunks of input rows.
• Intermediate - Partial aggregation results are processed to generate consolidated partial results.
• Final - Partial aggregation results are processed and any final calculations performed to generate the aggregation result.

Intra-partition sort type

The type of shared sort that is being used:

• Shared - One sort is created and subagents are synchronized during insertion into the sort. When the sort is completed, subagents use a parallel scan to read the sort.
• Replicated - This sort is used where all subagents are required to read every row of the sort. One sort is created and subagents are synchronized during insertion into the sort. When the sort is completed, each subagent reads the entire sort.
• Partitioned - A separate sort is created for each subagent. The subagents hash on the sort partition columns to determine into which sort they should insert a row. This partitions the data by value. When the sort is completed, each subagent reads from one of the sort partitions.
• Round-robin - A separate sort is created for each subagent. A round-robin clock type algorithm is used to determine into which sort a row should be inserted. This means that during the insert phase, subagents take turns being responsible for inserting into each of the sorts. When the sort is completed, each subagent reads from one of the sort partitions.

(This item is displayed only for intra-partition parallelism or full parallelism (intra-partition and inter-partition)).

Intra-partition columns

The columns that intra-partition parallelism has used to partition data between subagents when the value of Intra-partition sort type is set to Partitioned. (This item is displayed only for intra-partition parallelism or full parallelism (intra-partition and inter-partition)).

TBSCAN

For the TBSCAN operator, the following input arguments are shown:

Scan source

The source over which the scan occurs (either base tables or input plans).

Scanned table

The quantifier of the table to be scanned.

Columns retrieved

The set of columns to be accessed.

Sargable predicates

The set of predicates to be applied as sargable (that is, that can be resolved by the database manager by simple comparisons rather than by subqueries).

Scan direction

The direction of the scan (either forward or reverse).

Residual predicates

The set of additional predicates to be applied as residuals.

Prefetch

A flag indicating the type of prefetching. Possible values are:

• Sequential detection is enabled
• A positive number indicating the number of row identifiers (RIDs) for list prefetch
• Prefetching is not enabled.

Maximum pages

The maximum number of pages of the table expected to be read from disk. Possible values are:

• None - Prefetching is not enabled. Note: This is different from the prefetch argument, which indicates whether or not the optimizer expects a prefetch to be done. Run time is not influenced by the prefetch argument.
• All - All pages of the index or table are expected to be read. The decision about whether to do a prefetch is made at run time.
• A positive number - Indicates how many pages are expected to be read. The closer this number is to zero, the more the prefetch quantity will be reduced, or prefetch will not be done at all.

Lock intents

The lock modes that DB2 uses to access the data for the table, and optionally to access individual rows. Lock modes include share, exclusive, update, or next_key_share. They determine the level of concurrency that is possible. (Displayed if the scan is over the base table.)

For more information, see the section on locking in the Administration Guide .

Intra-partition scan type

Indicates if the index scan is performed in parallel by each subagent or if each subagent performs a private index scan.

• None - A private index scan.
• Local parallel - The index scan is performed in parallel by each subagent.

(This item is displayed only for intra-partition parallelism or full parallelism (intra-partition and inter-partition)).

Intra-partition scan unit

The scan granularity unit. That is, when the value of Intra-partition scan type is set to Local parallel, the assigned range is scan granularity number of scan units.

• Page - Each subagent gets scan granularity pages at a time.
• Row - Each subagent gets scan granularity rows at a time.

(This item is displayed only for intra-partition parallelism or full parallelism (intra-partition and inter-partition)).

(This item is available in Full details mode only.)

Intra-partition scan granularity

The number of scan units that a subagent is assigned when the value of Intra-partition scan type is set to Local parallel. (This item is displayed only for intra-partition parallelism or full parallelism (intra-partition and inter-partition)).

(This item is available in Full details mode only.)

TEMP

For the TEMP operator, the following input arguments are shown:

Materialization

The level of materialization applied to temporary tables. Possible values are:

Full

All rows are put into a temporary table before any rows are read from it.

Slow

Some rows are put into a temporary table and are read from it before all rows have been inserted into it.

Common subexpression

A flag that is true if the temporary table contains a common subexpression (that is, an access plan that is read by more than one SCAN).

Columns retrieved

The set of columns that will be stored in the temporary table.

Intra-partition shared

A flag that is set to true if the creation of the temporary table is shared between the subagents. (This item is displayed only for intra-partition parallelism or full parallelism (intra-partition and inter-partition)).

TQUEUE

For the TQUEUE operator (displayed only for intra-partition parallelism, inter-partition parallelism, or full parallelism (intra-partition and inter-partition)), the following input arguments are shown:

Merging

A flag, true or false, indicating if the table queue merges the input maintaining the sort order. (This item is displayed only for intra-partition parallelism, inter-partition parallelism, or full parallelism (intra-partition and inter-partition)).

(This item is available in Full details mode only.)

Unique

A flag, true or false, indicating if the table queue will perform duplicate elimination. (This item is displayed only for inter-partition parallelism or full parallelism (intra-partition and inter-partition)).

(This item is available in Full details mode only.)

Listener

A flag, true or false, indicating if the table queue waits for results from other nodes. (This item is displayed only for inter-partition parallelism or full parallelism (intra-partition and inter-partition)).

(This item is available in Full details mode only.)

Table queue type

A flag, local or non-local, indicating if the table queue supports multiple local subagents or intra-partition parallelism. (This item is displayed only for intra-partition parallelism or full parallelism (intra-partition and inter-partition)).

Table queue send type

The send mechanism the table queue uses (displayed only for inter-partition parallelism or full parallelism (intra-partition and inter-partition)):

• Broadcast - Data is sent to all members of the nodegroup associated with the partitioning map ID (PMID).
• Directed - Data is directed at specific nodes within the nodegroup.
• Scatter - Data is randomly sent to members within the nodegroup.
• Subquery stepping - The table queue is associated with a correlated subquery.

Table queue read type

The read mechanism that the table queue uses (displayed only for intra-partition parallelism, inter-partition parallelism, or full parallelism (intra-partition and inter-partition)):

• Ahead - Data is read as it is being received.
• Stepping - Data reading is controlled carefully, typically due to a CURSOR.
• Subquery stepping - The table queue is associated with a correlated subquery.

Intra-partition degree of parallelism

The number of subagents that will be used to write to this table queue. (This item is displayed only for intra-partition parallelism or full parallelism (intra-partition and inter-partition)).

Partition columns

The list of columns that the data is partitioned on. An empty list indicates that the data partitioning is uninteresting or undetermined. (This item is displayed only for inter-partition parallelism or full parallelism (intra-partition and inter-partition)).

Order columns

The list of columns that the rows passing through this table queue are ordered on. (This item is displayed only for intra-partition parallelism, inter-partition parallelism, or full parallelism (intra-partition and inter-partition)).

UNIQUE

For the UNIQUE operator, the following input argument is shown:

Key requirement

The ordered list of columns upon which values must be distinct.

UPDATE

For the UPDATE operator, the following input argument is shown:

Updated table

The name of the table whose rows are to be updated.