The INDEX_SCAN operator scans the index built on the key columns. The node description contains details about how a certain access path is scanned, such as lock_mode and scan_direction.
The INDEX_SCAN operator has no child nodes. The description field for this operator contains:
Token Followed by ... Data Type
fragment_id A sequential number assigned to the fragment. 0 is always the master executor and 1 is reserved for the EXPLAIN plan. Numbers 2 to n will be ESP or DAM fragments.
integer
parent_frag The fragment_id for the parent of the current fragment. The value is (none) for the master executor.
integer
fragment_type Master, ESP, or DP2. text
olt_optimization Indicates whether an optimization for short, simple operations is used. Value is used if this optimization is used. Its value is used or not
used.
text
olt_opt_lean Indicates for short, simple operations whether a further optimization that reduces the physical size of the plan is used. Its value is used or not
used.
text
key_columns Columns used as the primary key. expr(text) begin_key Expression of the begin key predicates. expr(text) end_key Expression of the end key predicates. expr(text) scan_type INDEX_SCAN followed by table or index name. text
SQL/MX Operators
HP NonStop SQL/MX Query Guide —640323-001 7- 28
INDEX_SCAN Operator
The following is an example of the INDEX_SCAN operator:
control query default nested_joins 'off'; control query default hash_joins 'off'; prepare TestQuery37b from
SELECT *
FROM customer LEFT JOIN nation ON c_nationkey = n_nationkey WHERE c_custkey > 1000 AND c_custkey < 1010
ORDER BY c_custkey; DESCRIPTION
fragment_id ... 2 parent_frag ... 0 fragment_type ... dp2
olt_optimization ... not used olt_opt_lean ... not used
scan_type ... subset scan of index
DETCAT.DETSCH.CX1(DETCAT.DETSCH.CUSTOMER) scan_direction ... forward
key_type ... simple
lock_mode ... not specified, defaulted to lock cursor
access_mode ... not specified, defaulted to read committed
columns_retrieved ... 3
scan_direction Direction in which table is scanned: forward or reverse.
text
lock_mode The lock mode specified: shared, exclusive, not specified (defaulted to lock cursor), or unknown.
text
access_mode The access specified: read uncommitted, skip conflict, read committed, stable, serializable, mx serializable, not specified (defaulted to read committed), or unknown.
text
key_type Simple or MDAM. text
executor_predicates Any predicate expression that is not a key predicate evaluated by the executor in DAM.
expr(text)
columns_retrieved Estimated number of columns to be returned. integer fast_replydata_move Indicates whether an optimization for returning
data from DAM is used. The value used is returned if this optimization is used.
text
fast_scan Indicates whether an optimization for a simple scan operation is used. The value used is returned if this optimization is used.
text
part_key_predicate Predicate expression specified on the
partitioning key. Displayed only if partitioning key differs from clustering key.
expr(text)
mdam_disjunct Disjunct key predicates used by MDAM. expr(text)
SQL/MX Operators
HP NonStop SQL/MX Query Guide —640323-001 7- 29 INDEX_SCAN_UNIQUE Operator fast_scan ... used fast_replydata_move .... used key_columns ... DETCAT.DETSCH.CX1.C_NATIONKEY, DETCAT.DETSCH.CX1.C_CUSTKEY executor_predicates .... (DETCAT.DETSCH.CX1.C_CUSTKEY > 1000) and(DETCAT.DETSCH.CX1.C_CUSTKEY < 1010) and (DETCAT.DETSCH.CX1.C_CUSTKEY = DETCAT.DETSCH.CX1.C_CUSTKEY) begin_key ... (DETCAT.DETSCH.CX1.C_NATIONKEY = <min>), (DETCAT.DETSCH.CX1.C_CUSTKEY = 1000) end_key ... (DETCAT.DETSCH.CX1.C_NATIONKEY = <max>), DETCAT.DETSCH.CX1.C_CUSTKEY = 1010)
INDEX_SCAN_UNIQUE Operator
DAM Unique Group
The INDEX_SCAN_UNIQUE operator describes a scan on the primary key column with an index on that column.
The INDEX_SCAN_UNIQUE operator has no child nodes. The description field for this operator contains:
Token Followed by ... Data Type
fragment_id A sequential number assigned to the fragment. 0 is always the master executor and 1 is reserved for the EXPLAIN plan. Numbers 2 to n will be ESP or DAM fragments.
integer
parent_frag The fragment_id for the parent of the current fragment. The value is (none) for the master executor.
integer
fragment_type Master, ESP, or DP2. text
olt_opt_lean Indicates for short, simple operations whether a further optimization that reduces the physical size of the plan is used. Its value is used or
not used.
text
key_columns Columns used as the primary key. expr(text) key Expression of the key predicate. expr(text) scan_type INDEX_SCAN_UNIQUE followed by table or
index name.
text
lock_mode The lock mode specified: shared, exclusive, not specified (defaulted to lock cursor), or unknown.
text
SQL/MX Operators
HP NonStop SQL/MX Query Guide —640323-001 7- 30
INDEX_SCAN_UNIQUE Operator
The following is an example of the INDEX_SCAN_UNIQUE operator:
prepare TestQuery14 from select * from supplier where s_nationkey = 12 and s_suppkey = 14;
DESCRIPTION
fragment_id ... 2 parent_frag ... 0 fragment_type ... dp2
olt_optimization ... not used olt_opt_lean ... not used scan_type ...
unique access of index
CAT.SCH.SX1(CAT.SCH.SUPPLIER) key_type ... simple
lock_mode ... not specified, defaulted to lock cursor
access_mode ... not specified, defaulted to read committed
columns_retrieved ... 3 fast_replydata_move .... used
key_columns ... CAT.SCH.SX1.S_NATIONKEY, CAT.SCH.SX1.S_SUPPKEY
executor_predicates .... (CAT.SCH.SX1.S_SUPPKEY = %(14)) and (CAT.SCH.SX1.S_SUPPKEY = %(14)) key ... (CAT.SCH.SX1.S_NATIONKEY = %(12)), (CAT.SCH.SX1.S_SUPPKEY = %(14))
access_mode The access specified: read uncommitted, skip conflict, read committed, stable, serializable, mx serializable, not specified (defaulted to read committed), or unknown.
text
executor_predicates Any predicate expression that is not a key predicate evaluated by the executor in DAM.
expr(text)
part_key_predicate Predicate expression specified on partitioning key. It is displayed only if partitioning key differs from clustering key.
expr(text)
columns_retrieved Estimated number of columns to be returned. integer fast_replydata_move Indicates whether an optimization for returning
data from DAM is used. The value used is returned if this optimization is used.
text
fast_scan Indicates whether an optimization for a simple scan operation is used. The value used is returned if this optimization is used.
text
olt_optimization Indicates whether an optimization for short, simple operations is used. The value used is returned if this optimization is used.
SQL/MX Operators
HP NonStop SQL/MX Query Guide —640323-001 7- 31
INSERT Operator