SQL/MX 2.x Query Guide (H06.04+, J06.03+)
SQL/MX Operators
HP NonStop SQL/MX Query Guide—540437-005
7-12
ESP_EXCHANGE Operator
insert into tabl3 values
(1,2,3,1,1),(4,5,6,1,1),(7,8,9,1,1),(1,2,3,1,1),(4,5,6,1,1),
(7,8,9,1,1),
(9,2,3,1,1),(4,5,6,1,1),(7,7,9,1,1),(1,9,3,1,1),(4,5,6,1,1),
(7,8,9,1,1),
(9,2,3,1,1),(4,4,6,1,1),(7,7,9,1,1),(1,9,3,1,1),(4,5,6,1,1),
(7,8,9,1,1),
(9,2,3,1,1),(4,4,6,1,1),(7,7,9,1,1),(1,9,3,1,1),(4,5,6,1,1),
(7,8,9,1,1),
(9,2,3,1,1),(4,4,6,1,1),(7,7,9,1,1),(1,9,3,1,1),(4,5,6,1,1),
(7,8,9,1,1),
(9,2,3,1,1),(4,4,6,1,1),(7,7,9,1,1),(1,9,3,1,1),(4,5,6,1,1),
(7,8,9,1,1),
(1,2,3,1,1),(4,4,6,1,1),(7,7,9,1,1),(1,9,3,1,1),(4,5,6,1,1),
(7,8,9,1,1),
(1,2,3,1,1),(4,4,6,1,1),(7,7,9,1,1),(1,9,3,1,1),(4,5,6,1,1),
(7,8,9,1,1),
(9,2,3,1,1),(4,4,6,1,1),(7,7,9,1,1),(1,9,3,1,1),(4,5,6,1,1),
(7,8,9,1,1),
(9,2,3,1,1),(4,4,6,1,1),(7,7,9,1,1),(1,9,3,1,1),(4,5,6,1,1),
(7,8,9,1,1),
(1,2,3,1,1),(4,4,6,1,1),(7,7,9,1,1),(1,9,3,1,1),(4,5,6,1,1),
(7,8,9,1,1),
(1,2,3,1,1),(4,4,6,1,1),(7,7,9,1,1),(1,9,3,1,1),(4,5,6,1,1),
(7,8,9,1,1),
(1,2,3,1,1),(4,4,6,1,1),(7,7,9,1,1),(1,9,3,1,1),(4,5,6,1,1),
(7,8,9,1,1),
(4,5,6,1,1),(7,8,9,1,1);
insert into tabl4 select * from tabl3;
insert into tabl3 select * from tabl4;
?section test
CONTROL QUERY DEFAULT CHECK_CONSTRAINT_PRUNING 'ON';
CONTROL QUERY DEFAULT INTERACTIVE_ACCESS 'ON';
CONTROL QUERY DEFAULT UNION_TRANSITIVE_PREDICATES 'ON';
CONTROL QUERY DEFAULT COMP_BOOL_25 'ON';
CONTROL QUERY DEFAULT MERGE_JOINS 'OFF';
CONTROL QUERY DEFAULT HASH_JOINS 'OFF';
CONTROL QUERY DEFAULT OPTIMIZER_PRUNING 'OFF';
CONTROL QUERY DEFAULT DATA_FLOW_OPTIMIZATION 'OFF';
CONTROL QUERY DEFAULT METADATA_CACHE_SIZE '200';
CONTROL QUERY DEFAULT OPTIMIZATION_LEVEL_1_SAFETY_NET '100000';
CONTROL QUERY DEFAULT MULTIUNION 'ON';
CONTROL QUERY DEFAULT ATTEMPT_ESP_PARALLELISM 'ON';
CONTROL QUERY DEFAULT DEF_NUM_SMP_CPUS '1';
CONTROL QUERY DEFAULT PARALLEL_NUM_ESPS '2';
CONTROL QUERY DEFAULT DETAILED_STATISTICS 'ALL';
control query shape
nested_join(esp_exchange(cut),esp_exchange(cut));
prepare vv from
select * from view30, view40 where view30.a = view40.b;