The FILE_SCAN operator contains details about how a certain access path is scanned, such as lock_mode and scan_direction.
The FILE_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 fragment of the current fragment. The value is (none) for the master executor.
integer
fragment_type Master, ESP, or DP2. text
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. Its value is used or
not used.
SQL/MX Operators
HP NonStop SQL/MX Query Guide —640323-001 7- 16
FILE_SCAN Operator
The following is an example of the FILE_SCAN operator:
PREPARE TestQuery2 FROM
SELECT s_acctbal, s_name, n_name, p_partkey, p_mfgr, s_address, s_phone, s_comment
FROM part,supplier,partsupp, nation, region WHERE p_partkey = ps_partkey
AND s_suppkey = ps_suppkey AND p_size = 15
AND p_type like '%BRASS'
AND s_nationkey = n_nationkey AND n_regionkey = r_regionkey AND r_name = 'EUROPE'
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
scan_type FILE_SCAN followed by table name. text 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
executor_predicate Any predicate expression that is not a key predicate evaluated by the executor in DAM.
expr(text)
columns_retrieved Estimate of the number of columns to be returned.
integer
fast_scan Indicates whether an optimization for a simple scan operation is used. The value used is returned if this optimization is used.
text
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
key_columns Columns used as the primary key. expr(text) mdam_disjunct Disjunct key predicates used by MDAM. expr(text) begin_key Expression of the begin key predicate. expr(text) end_key Expression of the end key predicate. expr(text)
key_type Simple or MDAM. text
part_key_predicate Predicate expression specified on partitioning key. Displayed only if partitioning key differs from clustering key.
expr(text)
SQL/MX Operators
HP NonStop SQL/MX Query Guide —640323-001 7- 17
FILE_SCAN_UNIQUE Operator
AND ps_supplycost = (SELECT MIN(ps_supplycost) FROM partsupp ps1,supplier s1, nation n1,region r1 WHERE p_partkey = ps1.ps_partkey
AND s1.s_suppkey = ps1.ps_suppkey AND s1.s_nationkey = n1.n_nationkey AND n1.n_regionkey = r1.r_regionkey AND r1.r_name = 'EUROPE')
ORDER BY s_acctbal desc, n_name, s_name, p_partkey; DESCRIPTION
fragment_id ... 10 parent_frag ... 0 fragment_type ... dp2
olt_optimization ... not used olt_opt_lean ... not used
scan_type ... subset scan of table
DETCAT.DETSCH.PARTSUPP PS1 scan_direction ... forward
key_type ... simple
lock_mode ... not specified, defaulted to lock cursor
access_mode ... not specified, defaulted to read committed
columns_retrieved ... 5 fast_scan ... used fast_replydata_move .... used
key_columns ... PS_PARTKEY, PS_SUPPKEY begin_key ... (PS_PARTKEY = DETCAT.DETSCH.PSX1.PS_PARTKEY), PS_SUPPKEY = <min>) end_key ... (PS_PARTKEY = DETCAT.DETSCH.PSX1.PS_PARTKEY), (PS_SUPPKEY = <max>)
FILE_SCAN_UNIQUE Operator
DAM Unique Group
The FILE_SCAN_UNIQUE operator describes a portion of an execution plan where you are scanning for a unique key value. It selects zero or one row.
SQL/MX Operators
HP NonStop SQL/MX Query Guide —640323-001 7- 18
FILE_SCAN_UNIQUE Operator
The FILE_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_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) key Expression of the key predicate. expr(text) scan_type Unique access of table, followed by table
name.
text
key_type Simple or MDAM. text
lock_mode The lock specified: read uncommitted, skip conflict, read committed, stable, serializable, mx serializable, not specified (defaulted to read committed), 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
columns_retrieved Estimate of the number of columns to be returned.
integer
executor_predicates Any predicate that is not a key predicate evaluated by the executor in DAM.
SQL/MX Operators
HP NonStop SQL/MX Query Guide —640323-001 7- 19
FILE_SCAN_UNIQUE Operator
The following is an example of the FILE_SCAN_UNIQUE operator:
PREPARE TestQuery2 FROM
SELECT s_acctbal, s_name, n_name, p_partkey, p_mfgr, s_address, s_phone, s_comment
FROM part,supplier,partsupp, nation, region WHERE p_partkey = ps_partkey
AND s_suppkey = ps_suppkey AND p_size = 15
AND p_type like '%BRASS'
AND s_nationkey = n_nationkey AND n_regionkey = r_regionkey AND r_name = 'EUROPE'
AND ps_supplycost = (SELECT MIN(ps_supplycost) FROM partsupp ps1,supplier s1, nation n1,region r1 WHERE p_partkey = ps1.ps_partkey
AND s1.s_suppkey = ps1.ps_suppkey AND s1.s_nationkey = n1.n_nationkey AND n1.n_regionkey = r1.r_regionkey AND r1.r_name = 'EUROPE')
ORDER BY s_acctbal desc, n_name, s_name, p_partkey; DESCRIPTION
fragment_id ... 3 parent_frag ... 0 fragment_type ... dp2
olt_optimization ... not used olt_opt_lean ... not used
scan_type ... unique access of table DETCAT.DETSCH.PART key_type ... simple
lock_mode ... not specified, defaulted to lock cursor
access_mode ... not specified, defaulted to read committed
columns_retrieved ... 9 fast_replydata_move .... used
key_columns ... P_PARTKEY
executor_predicates .... (P_TYPE like '%BRASS') and (P_SIZE = 15)
key ... (P_PARTKEY =
DETCAT.DETSCH.PSX1.PS_PARTKEY)
part_key_predicate Predicate expression specified on partitioning key. It is displayed only if partitioning key differs from clustering key.
expr(text)
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
SQL/MX Operators
HP NonStop SQL/MX Query Guide —640323-001 7- 20