description of esp_exchange operation doesn't have *h2-br* and *h2-ud*
Affects | Status | Importance | Assigned to | Milestone | |
---|---|---|---|---|---|
Trafodion |
Invalid
|
Medium
|
QF Chen |
Bug Description
SQL>set schema G_SKEWBUSTER;
SQL>cqd comp_bool_158 'on';
--- SQL operation complete.
SQL>cqd SKEW_ROWCOUNT_
--- SQL operation complete.
SQL>Cqd SKEW_SENSITIVIT
--- SQL operation complete.
SQL>prepare xx from select count(*) from fiso f, diso d where f.string20=
--- SQL command prepared.
SQL>explain options 'f' xx;
LC RC OP OPERATOR OPT DESCRIPTION CARD
---- ---- ---- -------
8 . 9 root 1.00E+000
7 . 8 sort_partial_
6 . 7 esp_exchange 1:20(hash2) 1.00E+000
5 . 6 sort_partial_
4 2 5 hybrid_hash_join 3.25E+006
3 . 4 esp_exchange 20(hash2):8(hash2) 1.62E+007
. . 3 trafodion_scan FISO 1.62E+007
1 . 2 esp_exchange 20(hash2):8(hash2) 1.00E+006
. . 1 trafodion_scan DISO 1.00E+006
SQL>showddl fiso;
CREATE TABLE TRAFODION.
(
UNIQUE1 INT NO DEFAULT NOT NULL NOT DROPPABLE
SERIALIZED
, ONEPERCENT INT NO DEFAULT NOT NULL NOT DROPPABLE
SERIALIZED
, TENPERCENT INT NO DEFAULT NOT NULL NOT DROPPABLE
SERIALIZED
, TWENTYPERCENT INT NO DEFAULT NOT NULL NOT DROPPABLE
SERIALIZED
, FIFTYPERCENT INT NO DEFAULT NOT NULL NOT DROPPABLE
SERIALIZED
, STRING1 CHAR(52) CHARACTER SET ISO88591 COLLATE
DEFAULT NO DEFAULT NOT NULL NOT DROPPABLE SERIALIZED
, STRING10 CHAR(52) CHARACTER SET ISO88591 COLLATE
DEFAULT NO DEFAULT NOT NULL NOT DROPPABLE SERIALIZED
, STRING20 CHAR(52) CHARACTER SET ISO88591 COLLATE
DEFAULT NO DEFAULT NOT NULL NOT DROPPABLE SERIALIZED
, STRING50 CHAR(52) CHARACTER SET ISO88591 COLLATE
DEFAULT NO DEFAULT NOT NULL NOT DROPPABLE SERIALIZED
, STRING1V VARCHAR(104) CHARACTER SET ISO88591
COLLATE DEFAULT NO DEFAULT NOT NULL NOT DROPPABLE SERIALIZED
, STRING10V VARCHAR(104) CHARACTER SET ISO88591
COLLATE DEFAULT NO DEFAULT NOT NULL NOT DROPPABLE SERIALIZED
, STRING20V VARCHAR(104) CHARACTER SET ISO88591
COLLATE DEFAULT NO DEFAULT NOT NULL NOT DROPPABLE SERIALIZED
, STRING50V VARCHAR(104) CHARACTER SET ISO88591
COLLATE DEFAULT NO DEFAULT NOT NULL NOT DROPPABLE SERIALIZED
, UNIQCHAR CHAR(104) CHARACTER SET ISO88591 COLLATE
DEFAULT NO DEFAULT NOT NULL NOT DROPPABLE SERIALIZED
, UNIQVCHAR VARCHAR(104) CHARACTER SET ISO88591
COLLATE DEFAULT NO DEFAULT NOT NULL NOT DROPPABLE SERIALIZED
, PRIMARY KEY (UNIQUE1 ASC)
)
SALT USING 8 PARTITIONS
;
--- SQL operation complete.
SQL>showddl diso;
CREATE TABLE TRAFODION.
(
UNIQUE1 INT NO DEFAULT NOT NULL NOT DROPPABLE
SERIALIZED
, STRINGVAR VARCHAR(52) CHARACTER SET ISO88591 COLLATE
DEFAULT NO DEFAULT NOT NULL NOT DROPPABLE SERIALIZED
, STRINGCHAR CHAR(52) CHARACTER SET ISO88591 COLLATE
DEFAULT NO DEFAULT NOT NULL NOT DROPPABLE SERIALIZED
, PRIMARY KEY (UNIQUE1 ASC)
)
SALT USING 8 PARTITIONS
;
--- SQL operation complete.
description: | updated |
Changed in trafodion: | |
milestone: | r1.1 → r2.0 |
Changed in trafodion: | |
status: | New → In Progress |
The root cause of the skew buster plan not being generated is that the length of the char column f.string20 is 52, longer than the value of the CQD USTAT_MAX_ CHAR_BOUNDARY_ LEN. This CQD controls the max length of the boundary values for the histogram.
When the CQD's value is shorter, the frequent values collected will not be useful to skew buster.
Suggest to increase the CQD value (e.g., to 52), repopulate the histogram and regenerate the plan.
In my test, I increased the CQD to 52. COMP_FLOAT_3 is increased to '10' in order to force the compiler to try SB plan due to my test setup (may not need in QA test).
>>cqd comp_bool_158 'on';
--- SQL operation complete. CHAR_BOUNDARY_ LEN '52';
>>
>>cqd USTAT_MAX_
--- SQL operation complete. THRESHOLD '0';
>>
>>cqd SKEW_ROWCOUNT_
--- SQL operation complete.
>>cqd COMP_FLOAT_3 '10';
--- SQL operation complete. Y_THRESHOLD '0';
>>
>>
>>Cqd SKEW_SENSITIVIT
--- SQL operation complete.
>>
>>prepare xx from select count(*) from cube1 f, cube2 d where f.txt =d.txt;
--- SQL command prepared.
>>
>>explain options 'f' xx;
LC RC OP OPERATOR OPT DESCRIPTION CARD ------- ------ -------- ------- ------- ------ ---------
---- ---- ---- -------
8 . 9 root 1.00E+000 aggr_ro 1.00E+000 aggr_le 1.00E+000
7 . 8 sort_partial_
6 . 7 esp_exchange 1:5(h2-ud) 1.00E+000
5 . 6 sort_partial_
4 2 5 hybrid_hash_join 2.00E+009
3 . 4 esp_exchange 5(h2-ud):4(hash2) 1.00E+005
. . 3 trafodion_scan CUBE1 1.00E+005
1 . 2 esp_exchange 5(h2-br):4(hash2) 2.00E+004
. . 1 trafodion_scan CUBE2 2.00E+004
--- SQL operation complete.