| >> |
| >> |
| >>create table t018_orders(o_orderkey integer not null primary key, |
| +> o_col2 integer) |
| +> salt using 4 partitions; |
| |
| --- SQL operation complete. |
| >> |
| >>create table t018_orders_ns(o_orderkey integer not null primary key, |
| +> o_col2 integer); |
| |
| --- SQL operation complete. |
| >> |
| >>create table t018_lineitem(l_orderkey integer not null, |
| +> l_linenum integer not null, |
| +> l_col3 integer, |
| +> primary key (l_orderkey)) |
| +> salt using 4 partitions; |
| |
| --- SQL operation complete. |
| >> |
| >>create table t018_lineitem_ns(l_orderkey integer not null, |
| +> l_linenum integer not null, |
| +> l_col3 integer, |
| +> primary key (l_orderkey)); |
| |
| --- SQL operation complete. |
| >> |
| >>control query default PARALLEL_NUM_ESPS '2' ; |
| |
| --- SQL operation complete. |
| >> |
| >>---------------------------------------------------------------------- |
| >>-- should see a co-located hash join |
| >>cqd hbase_hash2_partitioning 'ON'; |
| |
| --- SQL operation complete. |
| >> |
| >>prepare xx from |
| +>select count(*) from t018_orders /*+ cardinality 1e6 */ |
| +> join t018_lineitem /*+ cardinality 1e7 */ on o_orderkey = l_orderkey; |
| |
| --- SQL command prepared. |
| >> |
| >>explain options 'f' xx; |
| |
| LC RC OP OPERATOR OPT DESCRIPTION CARD |
| ---- ---- ---- -------------------- -------- -------------------- --------- |
| |
| 6 . 7 root 1.00E+000 |
| 5 . 6 sort_partial_aggr_ro 1.00E+000 |
| 4 . 5 esp_exchange 1:2(hash2) 1.00E+000 |
| 3 . 4 sort_partial_aggr_le 1.00E+000 |
| 2 1 3 hybrid_hash_join u 5.00E+012 |
| . . 2 trafodion_scan T018_LINEITEM 1.00E+007 |
| . . 1 trafodion_scan T018_ORDERS 1.00E+006 |
| |
| --- SQL operation complete. |
| >> |
| >>-- force range partition of the two tables |
| >>cqd hbase_hash2_partitioning 'OFF'; |
| |
| --- SQL operation complete. |
| >> |
| >>prepare xx from |
| +>select count(*) from t018_orders /*+ cardinality 1e6 */ |
| +> join t018_lineitem /*+ cardinality 1e7 */ on o_orderkey = l_orderkey; |
| |
| --- SQL command prepared. |
| >> |
| >>explain options 'f' xx; |
| |
| LC RC OP OPERATOR OPT DESCRIPTION CARD |
| ---- ---- ---- -------------------- -------- -------------------- --------- |
| |
| 7 . 8 root 1.00E+000 |
| 6 . 7 sort_partial_aggr_ro 1.00E+000 |
| 5 . 6 esp_exchange 1:2(range) 1.00E+000 |
| 4 . 5 sort_partial_aggr_le 1.00E+000 |
| 3 2 4 hybrid_hash_join u 5.00E+012 |
| . . 3 trafodion_scan T018_LINEITEM 1.00E+007 |
| 1 . 2 esp_exchange 2(rep-b):2(range) 1.00E+006 |
| . . 1 trafodion_scan T018_ORDERS 1.00E+006 |
| |
| --- SQL operation complete. |
| >> |
| >>-- non-salted tables should be single partitioned |
| >>cqd hbase_range_partitioning 'OFF'; |
| |
| --- SQL operation complete. |
| >>cqd hbase_hash2_partitioning 'ON'; |
| |
| --- SQL operation complete. |
| >> |
| >>prepare xx from |
| +>select count(*) from t018_orders_ns /*+ cardinality 1e6 */ |
| +> join t018_lineitem_ns /*+ cardinality 1e7 */ on o_orderkey = l_orderkey; |
| |
| --- SQL command prepared. |
| >> |
| >>explain options 'f' xx; |
| |
| LC RC OP OPERATOR OPT DESCRIPTION CARD |
| ---- ---- ---- -------------------- -------- -------------------- --------- |
| |
| 8 . 9 root 1.00E+000 |
| 7 . 8 sort_partial_aggr_ro 1.00E+000 |
| 6 . 7 esp_exchange 1:2(hash2) 1.00E+000 |
| 5 . 6 sort_partial_aggr_le 1.00E+000 |
| 4 2 5 hybrid_hash_join u 1.00E+011 |
| 3 . 4 esp_exchange 2(hash2):1 (m) 1.00E+007 |
| . . 3 trafodion_scan T018_LINEITEM_NS 1.00E+007 |
| 1 . 2 esp_exchange 2(hash2):1 (m) 1.00E+006 |
| . . 1 trafodion_scan T018_ORDERS_NS 1.00E+006 |
| |
| --- SQL operation complete. |
| >> |
| >>--test CQD PARALLEL_NUM_ESPS |
| >>control query default PARALLEL_NUM_ESPS '98'; |
| |
| --- SQL operation complete. |
| >> |
| >>prepare xx from |
| +>select count(*) from t018_orders /*+ cardinality 1e6 */ |
| +> join t018_lineitem /*+ cardinality 1e7 */ on o_orderkey = l_orderkey; |
| |
| --- SQL command prepared. |
| >> |
| >>explain options 'f' xx; |
| |
| LC RC OP OPERATOR OPT DESCRIPTION CARD |
| ---- ---- ---- -------------------- -------- -------------------- --------- |
| |
| 8 . 9 root 1.00E+000 |
| 7 . 8 sort_partial_aggr_ro 1.00E+000 |
| 6 . 7 esp_exchange 1:98(hash2) 1.00E+000 |
| 5 . 6 sort_partial_aggr_le 1.00E+000 |
| 4 2 5 hybrid_hash_join u 5.00E+012 |
| 3 . 4 esp_exchange 98(hash2):4(hash2) 1.00E+007 |
| . . 3 trafodion_scan T018_LINEITEM 1.00E+007 |
| 1 . 2 esp_exchange 98(hash2):4(hash2) 1.00E+006 |
| . . 1 trafodion_scan T018_ORDERS 1.00E+006 |
| |
| --- SQL operation complete. |
| >> |
| >>exit; |
| |
| End of MXCI Session |
| |
