Operator nodes

An operator node represents an action that is performed on data or on the output from a table or an index when the access plan is executed.

An access plan graph might contain any of the following operator nodes:

BTBSCAN
A BTBSCAN node indicates an operation called a buffer table scan, in which DB2® accesses the rows in a buffer table. The BTBSCAN node is the defining node for a buffer table scan construct.
CORSUB ACCESS
A CORSUB ACCESS node represents access by a correlated subquery.
DELETE
A DELETE node indicates the deletion of selected rows from a table or a deletable view. The DELETE node is the defining node of a delete construct.
DFETCH
A DFETCH node represents an operation called a direct fetch, in which DB2 directly accesses the rows in a table by using previously known ROWIDs.

The DFETCH node is the defining node for a direct fetch construct.

DIXSCAN
A DIXSCAN node represents DOCID index scan access, which returns a RID according to the DOCID.
EXCEPT
The EXCEPT node represents the EXCEPT operation.

An EXCEPT operation returns the rows in the outer table but not in the inner table, with redundant duplicate rows eliminated.

EXCEPTA
An EXCEPTA node represents an EXCEPT ALL operation.

An EXCEPT ALL operation returns the rows in the outer table but not in the inner table, with redundant duplicate rows retained.

FETCH
A FETCH node represents an operation in which DB2 fetches rows from a table using the RIDs from an IXSCAN or MIXSCAN.
FFETCH
An FFETCH node represents an operation in which DB2 uses a fact table index to fetch the fact table during a data manager pushdown star join.
FIXSCAN
A FIXSCAN node represents an operation called a fact table index scan, in which DB2 scans a fact table index during a data manager pushdown star join.
HSSCAN
An HSSCAN node represents an operation in which DB2 accesses a row using a fully qualified key and without using a traditional index. This access method employs a hashing technique to transform the key to a physical location of the row.
INLIST
An INLIST node represents an operation in which DB2 accesses an in-memory table that stores the IN-list elements.
INSERT
An INSERT node indicates the insertion of rows into a table or an insertable view. The INSERT node is the defining node of an insert construct.
INTERSECT
An INTERSECT node represents the INTERSECT operation.

An INTERSECT operation returns the rows both in the outer table and the inner table, with redundant duplicate rows eliminated. The INTERSECT node is the defining node of an INTERSECT construct.

INTERSECTA
An INTERSECTA node represents the INTERSECT ALL operation.

An INTERSECT ALL operation returns the rows both in the outer table and the inner table, with redundant duplicate rows retained.

IXAND
An IXAND node represents an operation in which DB2 returns the intersection of two sorted ROWID lists. Only those ROWIDs that exist in both ROWID lists are included in the output.
IXOR
An IXOR node represents an operation in which DB2 returns the union of two sorted ROWID lists. Any ROWID that exists in at least one of the ROWID lists is included in the output. Duplicate ROWIDs are removed from the output.
IXSCAN
An IXSCAN node represents a single-index scan. The IXSCAN node is the defining node for a single-index access construct.
MERGE
A MERGE node represents an operation in which DB2 merges multiple data streams into one data stream.
MERGE (statement)
The MERGE (statement) node represents the MERGE statement. It combines the conditional UPDATE and INSERT operation on a target table (or view) in a single statement.

A MERGE statement operation performs the following actions with values from a set of source rows:

  • Updates rows that satisfy the search condition in the ON clause in a target table
  • Inserts rows from the source table that do not satisfy the search condition.
MIXSCAN
A MIXSCAN node represents a multiple-index scan. The MIXSCAN node is the defining node for a multiple-index access construct.
PARTITION
A PARTITION node represents an operation in which DB2 separates one data stream into multiple data streams.
RGLIST
A RGLIST node represents a range-list access, which is used to simplify the processing of OR predicates. The RGLIST node is the defining node for a range-list access construct.
REPARTITION
A repartition node represents an operation in which DB2 re-partitions multiple input data streams into multiple output data streams.
RID FETCH
A RID FETCH node represents RID fetch access, which is used to access data by using the built-in RID function.
SIXSCAN
A SIXSCAN node indicates an operation called a sparse index scan, in which DB2 accesses a sparse index. The SIXSCAN node is the defining node for a sparse index scan construct.
SORT
A SORT node indicates an operation in which DB2 sorts the rows from previous operations based on the sort keys. This operation always produces a work file. The SORT node is the defining node for a sort operation construct.
SORTRID
A SORTRID node represents an operation in which DB2 sorts the qualified index entries that result from an index scan based on the ascending order of ROWIDs.
TBSCAN
A TBSCAN node indicates a table space scan on a table, a work file, a materialized query table, or a pipe. A TBSCAN node is the defining node for a table space scan construct.
TRUNCATE
A TRUNCATE node represents a TRUNCATE statement, which deletes all rows for either base tables or declared global temporary tables.

The TRUNCATE statement deletes all rows for either base tables or declared global temporary tables. The base table can be in a simple table space, a segmented table space, a partitioned table space, or a universal table space. If the table contains LOB or XML columns, the corresponding table spaces and indexes are also truncated.

UNION
A UNION node represents the union of the results from two SELECT statements to form a single result table that contains no duplicate rows. The UNION node is the defining node of a UNION construct.
UNIONA
A UNIONA node represents the union of the results from two SELECT statements to form a single result table that might contain duplicate rows. The UNIONA node is the defining node of a UNION ALL construct.
UPDATE
An UPDATE node indicates the updating of one or more columns of the selected rows in a table or an updatable view. The UPDATE node is the defining node of an update construct.
WFSCAN
WFSCAN node indicates an operation called a work file scan, in which DB2 performs a scan on a work file that is labeled as WORKFILE. The WFSCAN node is the defining node for work file scan construct.
XIXAND
A XIXAND node represents an operation for XML data in which DB2 returns the intersection of two sorted DOCID lists. Only those DOCIDs that exist in both DOCID lists are included in the output.
XIXOR
A XIXOR node represents an operation for XML data in which DB2 returns the union of two sorted DOCID lists. Any DOCID that exists in at least one of the DOCID lists is included in the output. Duplicate DOCIDs are removed from the output.
XIXSCAN
A XIXSCAN node represents XML index scan access, which returns the DOCID and NODEID pairs according the key value.

Feedback