SQL/MX 2.x Reference Manual (H06.10+, J06.03+)

SQL/MX Statements
HP NonStop SQL/MX Reference Manual—544517-008
2-46
Examples of CONTROL QUERY SHAPE
parser/SqlParser.y
regress/compGeneral/EXPECTED018
regress/compGeneral/TEST018
•
This example shows how a scan can be forced to use an index:
>>prepare xx from
+>select * from part where p_partkey = (select
max(ps_partkey) from partsupp);
--- SQL command prepared.
>>explain options 'f' xx;
LC RC OP OPERATOR OPT DESCRIPTION CARD
-- -- -- ------------- --- ----------- ----------
7 . 8 root 1.00E+000
4 6 7 nested_join 1.00E+000
5 . 6 partition_access 1.00E+000
. . 5 file_scan_unique fr PART (s) 1.00E+000
3 . 4 partition_access 1.00E+000
2 . 3 shortcut_scalar_aggr 1.00E+000
1 . 2 firstn 1.00E+000
. . 1 index_scan PSX2 (s) 1.00E+002
--- SQL operation complete.
Force the scan to go through the index, which could be more efficient:
>>control query shape
nested_join(shortcut_groupby(split_top_pa(scan(path
'TPCDF.SF100f.PSX2'),
+>group , 72)), nested_join(exchange(scan(path
'TPCDF.SF100F.PX1')),
+>exchange(scan(path 'TPCDF.SF100F.PART')), INDEXJOIN));
--- SQL operation complete.
Prepare the statement using the forced scan:
>>prepare xx from
+>select * from part where p_partkey = (select
max(ps_partkey) from partsupp);
--- SQL command prepared.
>>explain options 'f' xx;
LC RC OP OPERATOR OPT DESCRIPTION CARD
-- -- -- ------------- --- ----------- ----------
13 . 14 root 1.00E+000
5 12 13 nested_join 1.00E+000
8 11 12 nested_join 1.00E+000
10 . 11 split_top 1:72(logph) 1.00E+000
9 . 10 partition_access 1.00E+000
. . 9 file_scan_unique fr PART (s) 1.00E+000
7 . 8 split_top 1:72(logph) 6.66E+006
6 . 7 partition_access 6.66E+006
. . 6 index_scan fr PX1 (s) 6.66E+006