description of esp_exchange operation doesn't have *h2-br* and *h2-ud*

[yzhang8]

SQL>set schema G_SKEWBUSTER;

SQL>cqd comp_bool_158 'on';

--- SQL operation complete.

SQL>cqd SKEW_ROWCOUNT_THRESHOLD '0';

--- SQL operation complete.

SQL>Cqd SKEW_SENSITIVITY_THRESHOLD '0';

--- SQL operation complete.

SQL>prepare xx from select count(*) from fiso f, diso d where f.string20=d.stringvar and f.unique1 > 16000000;

--- 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_aggr_ro 1.00E+000
6 . 7 esp_exchange 1:20(hash2) 1.00E+000
5 . 6 sort_partial_aggr_le 1.00E+000
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.G_SKEWBUSTER.FISO
  (
    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.G_SKEWBUSTER.DISO
  (
    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.

[QF Chen (qifan-chen)]

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.
>>
>>cqd USTAT_MAX_CHAR_BOUNDARY_LEN '52';

--- SQL operation complete.
>>
>>cqd SKEW_ROWCOUNT_THRESHOLD '0';

--- SQL operation complete.
>>cqd COMP_FLOAT_3 '10';

--- SQL operation complete.
>>
>>
>>Cqd SKEW_SENSITIVITY_THRESHOLD '0';

--- 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
7 . 8 sort_partial_aggr_ro 1.00E+000
6 . 7 esp_exchange 1:5(h2-ud) 1.00E+000
5 . 6 sort_partial_aggr_le 1.00E+000
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.