SQL/MX 2.x Reference Manual (G06.24+, H06.03+)

SQL/MX Statements
HP NonStop SQL/MX Reference Manual—523725-004
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.
>>display_explain options 'f' xx;
LC RC OP OPERATOR OPT DESCRIPTION
CARD
--- --- --- -------------------- -------- --------------
------ -------
9 . 10 root
1.00E+0
5 8 9 nested_join
1.00E+0
7 . 8 split_top 1:72(logph)
1.00E+0
6 . 7 partition_access
1.00E+0
. . 6 file_scan_unique fr PART (s)
1.00E+0
4 . 5 shortcut_scalar_aggr
1.00E+0
3 . 4 firstn
1.00E+0
2 . 3 split_top 1:72(logph)
8.00E+7
1 . 2 partition_access
8.00E+7
. . 1 index_scan fr PSX2 (s)
8.00E+7
--- 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