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

Bug #1440985 reported by yzhang8
6
This bug affects 1 person
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_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.

Tags: sql-cmp
yzhang8 (yan-zhang15)
description: updated
Changed in trafodion:
milestone: r1.1 → r2.0
QF Chen (qifan-chen)
Changed in trafodion:
status: New → In Progress
Revision history for this message
QF Chen (qifan-chen) wrote :

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.

Changed in trafodion:
status: In Progress → Invalid
To post a comment you must log in.
This report contains Public information  
Everyone can see this information.

Other bug subscribers

Remote bug watches

Bug watches keep track of this bug in other bug trackers.