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-15
ESP_EXCHANGE Operator
The following is an example of the ESP_EXCHANGE operator:
control query shape off;
drop table t1;
-- Assuming that the node has a data-volume $data04. Otherwise use any other
data-volume.
create table t1 (
a char(5) not null ,
b smallint not null ,
c char(4),
d integer,
primary key (a,b) )
partition (add first key ('MMMMM',20) location $data04);
insert into t1 values ('AAAA ',11,'AAAA',11);
insert into t1 values ('BBBB ',12,'BBBB',12);
insert into t1 values ('MMMM ',21,'MMMM',21);
insert into t1 values ('XXXXX',22,'XXXX',22);
control query default attempt_esp_parallelism 'on';
control query shape esp_exchange(hash_groupby(
esp_exchange(split_top_pa(scan))));
prepare xx from
select d, count(*) from t1 group by d;
explain options 'f' xx;
LC RC OP OPERATOR OPT DESCRIPTION CARD
--- ---- ---- -------------------- ---- ------------- ------
6 . 7 root 2.00E+000
5 . 6 esp_exchange 1:4(hash2) 2.00E+000
4 . 5 hash_groupby 2.00E+000
3 . 4 esp_exchange 4(hash2):2(range) 1.00E+002
2 . 3 split_top 2(range):2(range) 1.00E+002
1 . 2 partition_access 1.00E+002
. . 1 file_scan fs fr T1 (s) 1.00E+002
TP663851.fm Page 15 Wednesday, January 30, 2013 5:37 PM