SQL/MX 3.2.1 Query Guide (H06.26+, J06.15+)

SQL/MX Operators
HP NonStop SQL/MX Release 3.2.1 Query Guide—663851-003
7-22
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
Token Followed by ... Data Type
TP663851.fm Page 22 Wednesday, January 30, 2013 5:37 PM