| >> |
| >>prepare reporter from |
| +>select seq_num, inst_num, tdb_name, est_rows, act_rows |
| +>from table(statistics(NULL,'XX')) |
| +>where TDB_NAME like '%SPLIT_BOTTOM%' |
| +>order by seq_num, tdb_name, inst_num; |
| |
| --- SQL command prepared. |
| >> |
| >> |
| >>obey TEST013(setup); |
| >>-------------------------------------------------------------------------- |
| >>create schema trafodion.hbase; |
| |
| --- SQL operation complete. |
| >>-- CHAR ---- |
| >>create table char8(a char(10) not null not droppable, b char(20), primary key (a)) ; |
| |
| --- SQL operation complete. |
| >> |
| >>insert into char8 values |
| +>('1', '1'), ('2', '2'), ('3', '3'), ('4', '4'), ('5', '5'), |
| +>('6', '6'), ('7', '7'), ('8', '8'), ('9', '9'), ('10', '10'); |
| |
| --- 10 row(s) inserted. |
| >> |
| >>update statistics for table char8 clear; |
| |
| --- SQL operation complete. |
| >>update statistics for table char8 on every column; |
| |
| --- SQL operation complete. |
| >> |
| >>-- DATE ---- |
| >> |
| >>create table date8 (a date not null not droppable, b int, primary key (a)); |
| |
| --- SQL operation complete. |
| >> |
| >>insert into date8 values |
| +>(date '01/02/2013', 1), |
| +>(date '02/02/2013', 2), |
| +>(date '03/02/2013', 3), |
| +>(date '04/02/2013', 4), |
| +>(date '05/02/2013', 5), |
| +>(date '06/02/2013', 6), |
| +>(date '07/02/2013', 7), |
| +>(date '08/02/2013' ,8), |
| +>(date '09/02/2013', 9), |
| +>(date '10/02/2013', 10); |
| |
| --- 10 row(s) inserted. |
| >> |
| >>update statistics for table date8 clear; |
| |
| --- SQL operation complete. |
| >>update statistics for table date8 on every column; |
| |
| --- SQL operation complete. |
| >> |
| >>-- NUMERIC ---- |
| >> |
| >>create table numeric10dot2 (a numeric(10,2) not null not droppable, b int, primary key (a)) salt using 12 partitions ; |
| |
| --- SQL operation complete. |
| >> |
| >>insert into numeric10dot2 values |
| +>(1.20, 1), (1.30, 2), (1.40, 3), (1.50, 4), (1.60, 1), |
| +>(1.70, 2), (1.80, 3), (1.90, 4), (2.00, 3), (2.10, 3); |
| |
| --- 10 row(s) inserted. |
| >> |
| >>update statistics for table numeric10dot2 clear; |
| |
| --- SQL operation complete. |
| >>update statistics for table numeric10dot2 on every column; |
| |
| --- SQL operation complete. |
| >> |
| >>-- TIME ---- |
| >> |
| >>create table time2 (a time(2) not null not droppable, b int, primary key (a)) |
| +>salt using 8 partitions ; |
| |
| --- SQL operation complete. |
| >> |
| >>insert into time2 values (time '12:01:01', 1), |
| +>(time '12:01:02', 2), (time '12:01:03', 3), (time '12:01:04', 4), |
| +>(time '12:01:05', 5), (time '12:01:06', 6), (time '12:01:07', 7), |
| +>(time '12:01:08', 8), (time '12:01:09', 9), (time '12:01:19', 10); |
| |
| --- 10 row(s) inserted. |
| >> |
| >> |
| >>update statistics for table time2 clear; |
| |
| --- SQL operation complete. |
| >>update statistics for table time2 on every column; |
| |
| --- SQL operation complete. |
| >> |
| >>-- TIMESTAMP ---- |
| >> |
| >>create table timestamp2 (a timestamp(2) not null not droppable, b int, |
| +> primary key (a)) ; |
| |
| --- SQL operation complete. |
| >> |
| >>insert into timestamp2 values |
| +>(timestamp '2014-01-01 12:01:01.4', 1), |
| +>(timestamp '2014-01-01 12:01:02.4', 2), |
| +>(timestamp '2014-01-01 12:01:03.12', 3), |
| +>(timestamp '2014-01-01 12:01:04.13', 4), |
| +>(timestamp '2014-01-01 12:01:05.14', 5), |
| +>(timestamp '2014-01-01 12:01:06.19', 6), |
| +>(timestamp '2014-01-01 12:01:07.20', 7), |
| +>(timestamp '2014-01-01 12:01:08.21', 8), |
| +>(timestamp '2014-01-01 12:01:09.30', 9), |
| +>(timestamp '2014-01-01 12:01:19.31', 10); |
| |
| --- 10 row(s) inserted. |
| >> |
| >> |
| >>update statistics for table timestamp2 clear; |
| |
| --- SQL operation complete. |
| >>update statistics for table timestamp2 on every column; |
| |
| --- SQL operation complete. |
| >> |
| >> |
| >> |
| >>cqd risk_premium_serial '1.2'; |
| |
| --- SQL operation complete. |
| >>cqd HBASE_MIN_BYTES_PER_ESP_PARTITION '10'; |
| |
| --- SQL operation complete. |
| >>control query default detailed_statistics 'ALL'; |
| |
| --- SQL operation complete. |
| >>cqd query_cache '0'; |
| |
| --- SQL operation complete. |
| >>cqd HBASE_RANGE_PARTITIONING 'SYSTEM'; |
| |
| --- SQL operation complete. |
| >>cqd PARALLEL_NUM_ESPS '8'; |
| |
| --- SQL operation complete. |
| >>cqd MAX_ESPS_PER_CPU_PER_OP '4'; |
| |
| --- SQL operation complete. |
| >> |
| >>obey TEST013(test_char); |
| >>-------------------------------------------------------------------------- |
| >> |
| >>prepare xx from |
| +>SELECT b, count(b) FROM char8 <<+ cardinality 10e8 >> group by b ; |
| |
| --- SQL command prepared. |
| >> |
| >>explain options 'f' xx; |
| |
| LC RC OP OPERATOR OPT DESCRIPTION CARD |
| ---- ---- ---- -------------------- -------- -------------------- --------- |
| |
| 4 . 5 root 1.00E+001 |
| 3 . 4 esp_exchange 1:8(hash2) 1.00E+001 |
| 2 . 3 hash_groupby 1.00E+001 |
| 1 . 2 esp_exchange 8(hash2):1 1.00E+009 |
| . . 1 trafodion_scan CHAR8 1.00E+009 |
| |
| --- SQL operation complete. |
| >>execute xx; |
| |
| B (EXPR) |
| -------------------- -------------------- |
| |
| 9 1 |
| 8 1 |
| 10 1 |
| 1 1 |
| 3 1 |
| 2 1 |
| 4 1 |
| 5 1 |
| 7 1 |
| 6 1 |
| |
| --- 10 row(s) selected. |
| >>execute reporter; |
| |
| SEQ_NUM INST_NUM TDB_NAME EST_ROWS ACT_ROWS |
| ---------- ---------- ------------------------ -------------------- -------------------- |
| |
| 2 0 EX_SPLIT_BOTTOM 1000000000 10 |
| 4 0 EX_SPLIT_BOTTOM 10 2 |
| 4 1 EX_SPLIT_BOTTOM 10 1 |
| 4 2 EX_SPLIT_BOTTOM 10 2 |
| 4 3 EX_SPLIT_BOTTOM 10 1 |
| 4 4 EX_SPLIT_BOTTOM 10 2 |
| 4 5 EX_SPLIT_BOTTOM 10 0 |
| 4 6 EX_SPLIT_BOTTOM 10 1 |
| 4 7 EX_SPLIT_BOTTOM 10 1 |
| |
| --- 9 row(s) selected. |
| >> |
| >> |
| >>obey TEST013(test_date); |
| >>-------------------------------------------------------------------------- |
| >> |
| >>prepare xx from |
| +>SELECT b, count(b) FROM date8 <<+ cardinality 10e8 >> group by b ; |
| |
| --- SQL command prepared. |
| >> |
| >>explain options 'f' xx; |
| |
| LC RC OP OPERATOR OPT DESCRIPTION CARD |
| ---- ---- ---- -------------------- -------- -------------------- --------- |
| |
| 4 . 5 root 1.00E+001 |
| 3 . 4 esp_exchange 1:8(hash2) 1.00E+001 |
| 2 . 3 hash_groupby 1.00E+001 |
| 1 . 2 esp_exchange 8(hash2):1 1.00E+009 |
| . . 1 trafodion_scan DATE8 1.00E+009 |
| |
| --- SQL operation complete. |
| >>execute xx; |
| |
| B (EXPR) |
| ----------- -------------------- |
| |
| 10 1 |
| 5 1 |
| 7 1 |
| 6 1 |
| 3 1 |
| 8 1 |
| 9 1 |
| 1 1 |
| 4 1 |
| 2 1 |
| |
| --- 10 row(s) selected. |
| >>execute reporter; |
| |
| SEQ_NUM INST_NUM TDB_NAME EST_ROWS ACT_ROWS |
| ---------- ---------- ------------------------ -------------------- -------------------- |
| |
| 2 0 EX_SPLIT_BOTTOM 1000000000 10 |
| 4 0 EX_SPLIT_BOTTOM 10 1 |
| 4 1 EX_SPLIT_BOTTOM 10 0 |
| 4 2 EX_SPLIT_BOTTOM 10 1 |
| 4 3 EX_SPLIT_BOTTOM 10 3 |
| 4 4 EX_SPLIT_BOTTOM 10 1 |
| 4 5 EX_SPLIT_BOTTOM 10 0 |
| 4 6 EX_SPLIT_BOTTOM 10 2 |
| 4 7 EX_SPLIT_BOTTOM 10 2 |
| |
| --- 9 row(s) selected. |
| >> |
| >>obey TEST013(test_numeric); |
| >>-------------------------------------------------------------------------- |
| >> |
| >>prepare xx from |
| +>SELECT b, count(b) FROM numeric10dot2 <<+ cardinality 10e10 >> |
| +>group by b ; |
| |
| --- SQL command prepared. |
| >> |
| >>explain options 'f' xx; |
| |
| LC RC OP OPERATOR OPT DESCRIPTION CARD |
| ---- ---- ---- -------------------- -------- -------------------- --------- |
| |
| 4 . 5 root 4.00E+000 |
| 3 . 4 hash_partial_groupby 4.00E+000 |
| 2 . 3 esp_exchange 1:6(hash2) 4.00E+000 |
| 1 . 2 hash_partial_groupby 4.00E+000 |
| . . 1 trafodion_scan NUMERIC10DOT2 1.00E+011 |
| |
| --- SQL operation complete. |
| >>execute xx; |
| |
| B (EXPR) |
| ----------- -------------------- |
| |
| 2 2 |
| 3 4 |
| 1 2 |
| 4 2 |
| |
| --- 4 row(s) selected. |
| >>execute reporter; |
| |
| SEQ_NUM INST_NUM TDB_NAME EST_ROWS ACT_ROWS |
| ---------- ---------- ------------------------ -------------------- -------------------- |
| |
| 3 0 EX_SPLIT_BOTTOM 4 2 |
| 3 1 EX_SPLIT_BOTTOM 4 1 |
| 3 2 EX_SPLIT_BOTTOM 4 3 |
| 3 3 EX_SPLIT_BOTTOM 4 0 |
| 3 4 EX_SPLIT_BOTTOM 4 1 |
| 3 5 EX_SPLIT_BOTTOM 4 1 |
| |
| --- 6 row(s) selected. |
| >> |
| >> |
| >>obey TEST013(test_time); |
| >>-------------------------------------------------------------------------- |
| >>prepare xx from |
| +>SELECT b, count(b) FROM time2 <<+ cardinality 10e8 >> |
| +>group by b |
| +>; |
| |
| --- SQL command prepared. |
| >> |
| >>explain options 'f' xx; |
| |
| LC RC OP OPERATOR OPT DESCRIPTION CARD |
| ---- ---- ---- -------------------- -------- -------------------- --------- |
| |
| 4 . 5 root 1.00E+001 |
| 3 . 4 hash_partial_groupby 1.00E+001 |
| 2 . 3 esp_exchange 1:8(hash2) 1.00E+001 |
| 1 . 2 hash_partial_groupby 1.00E+001 |
| . . 1 trafodion_scan TIME2 1.00E+009 |
| |
| --- SQL operation complete. |
| >>execute xx; |
| |
| B (EXPR) |
| ----------- -------------------- |
| |
| 6 1 |
| 10 1 |
| 2 1 |
| 8 1 |
| 3 1 |
| 9 1 |
| 7 1 |
| 5 1 |
| 1 1 |
| 4 1 |
| |
| --- 10 row(s) selected. |
| >>execute reporter; |
| |
| SEQ_NUM INST_NUM TDB_NAME EST_ROWS ACT_ROWS |
| ---------- ---------- ------------------------ -------------------- -------------------- |
| |
| 3 0 EX_SPLIT_BOTTOM 10 0 |
| 3 1 EX_SPLIT_BOTTOM 10 2 |
| 3 2 EX_SPLIT_BOTTOM 10 1 |
| 3 3 EX_SPLIT_BOTTOM 10 1 |
| 3 4 EX_SPLIT_BOTTOM 10 1 |
| 3 5 EX_SPLIT_BOTTOM 10 1 |
| 3 6 EX_SPLIT_BOTTOM 10 2 |
| 3 7 EX_SPLIT_BOTTOM 10 2 |
| |
| --- 8 row(s) selected. |
| >> |
| >> |
| >>obey TEST013(test_timestamp); |
| >>-------------------------------------------------------------------------- |
| >>prepare xx from |
| +>SELECT b, count(b) FROM timestamp2 <<+ cardinality 10e8 >> |
| +>group by b |
| +>; |
| |
| --- SQL command prepared. |
| >> |
| >>explain options 'f' xx; |
| |
| LC RC OP OPERATOR OPT DESCRIPTION CARD |
| ---- ---- ---- -------------------- -------- -------------------- --------- |
| |
| 4 . 5 root 1.00E+001 |
| 3 . 4 esp_exchange 1:8(hash2) 1.00E+001 |
| 2 . 3 hash_groupby 1.00E+001 |
| 1 . 2 esp_exchange 8(hash2):1 1.00E+009 |
| . . 1 trafodion_scan TIMESTAMP2 1.00E+009 |
| |
| --- SQL operation complete. |
| >>execute xx; |
| |
| B (EXPR) |
| ----------- -------------------- |
| |
| 10 1 |
| 8 1 |
| 6 1 |
| 3 1 |
| 2 1 |
| 9 1 |
| 1 1 |
| 4 1 |
| 5 1 |
| 7 1 |
| |
| --- 10 row(s) selected. |
| >>execute reporter; |
| |
| SEQ_NUM INST_NUM TDB_NAME EST_ROWS ACT_ROWS |
| ---------- ---------- ------------------------ -------------------- -------------------- |
| |
| 2 0 EX_SPLIT_BOTTOM 1000000000 10 |
| 4 0 EX_SPLIT_BOTTOM 10 1 |
| 4 1 EX_SPLIT_BOTTOM 10 0 |
| 4 2 EX_SPLIT_BOTTOM 10 1 |
| 4 3 EX_SPLIT_BOTTOM 10 3 |
| 4 4 EX_SPLIT_BOTTOM 10 1 |
| 4 5 EX_SPLIT_BOTTOM 10 0 |
| 4 6 EX_SPLIT_BOTTOM 10 2 |
| 4 7 EX_SPLIT_BOTTOM 10 2 |
| |
| --- 9 row(s) selected. |
| >> |
| >> |
| >> |
| >>log; |
