SSC-PRF-TD0001

CLUSTER BY performance review.

Description

Marks where the usage of CLUSTER BY may cause performance issues.

Example Code

Teradata:

IN -> Teradata_01.sql

CREATE MULTISET TABLE T_2008,
NO FALLBACK,
NO BEFORE JOURNAL,
NO AFTER JOURNAL,
CHECKSUM = DEFAULT,
DEFAULT MERGEBLOCKRATIO
(
      COL1 NUMBER(20,0) NOT NULL,
      COL2 INTEGER,
      COL3 VARCHAR(4) CHARACTER SET LATIN NOT CASESPECIFIC,
      COL4 DATE FORMAT 'YYYY-MM-DD'
)
PRIMARY INDEX 
( 
      COL1, COL2
)
PARTITION BY ( RANGE_N(COL4 BETWEEN DATE '2010-01-01' AND DATE '2025-12-31' EACH INTERVAL '1' YEAR ),
CASE_N(
COL3  = 'T',
COL3 = 'M',
COL3 = 'L') ); -- PARTITION BY transformed to CLUSTER BY

Snowflake Scripting:

OUT -> Teradata_01.sql

CREATE TABLE T_2008
(
      COL1 NUMBER(20,0) NOT NULL,
      COL2 INTEGER,
      COL3 VARCHAR(4) COLLATE 'en-ci' /*** SSC-PRF-0002 - CASE INSENSITIVE COLUMNS CAN DECREASE THE PERFORMANCE OF QUERIES ***/,
      COL4 DATE
)
--** SSC-PRF-TD0001 - PERFORMANCE REVIEW - CLUSTER BY **
CLUSTER BY (RANGE_N(COL4 BETWEEN DATE '2010-01-01' AND DATE '2025-12-31' EACH INTERVAL '1' YEAR ),
CASE_N(
COL3  = 'T',
COL3 = 'M',
COL3 = 'L'))
COMMENT = '{"origin":"sf_sc","name":"snowconvert","version":{"major":1, "minor":0},{"attributes":{"component":"teradata"}}'
; -- PARTITION BY transformed to CLUSTER BY

Recommendations

• Review the code in order to identify possible performance issues. More information about this topic can be read here.

• If you need more support, you can email us at [e-mail]