>>
>>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;
