SQL/MX 3.1 Query Guide (H06.23+, J06.12+)

ManualsBrandsHP ManualsServerHP Integrity NonStop H-Series

141

142

143

144

145

146

147

148

149

150

SQL/MX Operators

HP NonStop SQL/MX Release 3.1 Query Guide—663851-001

7-15

ESP_EXCHANGE Operator

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;

DESCRIPTION

fragment_id ........... 13

parent_frag ............ 0

fragment_type .......... esp

buffer_size ............ 6,250

record_length ......... 40

parent_processes ....... 1

child_processes ........ 2

child_partitioning_func hash partitioned 2 ways on

(([0]ValueIdUnion(\DMR11.$DATA04.ZSDWC1HX.ZVB7V200

.A, \DMR11.$DATA04.ZSDWC1HX.ZVB7V200.A,

\DMR11.$DATA04.ZSDWC1HX.ZVB7V200.A,

\DMR11.$DATA04.ZSDWC1HX.ZVB7V200.A),

[0]ValueIdUnion(\DMR11.$DATA04.ZSDWC1HX.ZVB7V200.B

, \DMR11.$DATA04.ZSDWC1HX.ZVB7V200.B,

\DMR11.$DATA04.ZSDWC1HX.ZVB7V200.B,

\DMR11.$DATA04.ZSDWC1HX.ZVB7V200.B),

[0]ValueIdUnion(\DMR11.$DATA04.ZSDWC1HX.ZVB7V200.C

, \DMR11.$DATA04.ZSDWC1HX.ZVB7V200.C,

\DMR11.$DATA04.ZSDWC1HX.ZVB7V200.C,

\DMR11.$DATA04.ZSDWC1HX.ZVB7V200.C),

[0]ValueIdUnion(\DMR11.$DATA04.ZSDWC1HX.ZVB7V200.D

, \DMR11.$DATA04.ZSDWC1HX.ZVB7V200.D,

\DMR11.$DATA04.ZSDWC1HX.ZVB7V200.D,

\DMR11.$DATA04.ZSDWC1HX.ZVB7V200.D),

[0]ValueIdUnion(\DMR11.$DATA04.ZSDWC1HX.ZVB7V200.E

, \DMR11.$DATA04.ZSDWC1HX.ZVB7V200.E,

\DMR11.$DATA04.ZSDWC1HX.ZVB7V200.E,

\DMR11.$DATA04.ZSDWC1HX.ZVB7V200.E)))

bottom_partition_input_ \:_sys_HostVarLoHashPart,

\:_sys_HostVarHiHashPart

TP663851.fm Page 15 Monday, October 17, 2011 11:48 AM