MSCEWI2008

CLUSTER BY performance review.

This is a deprecated version of the SnowConvert documentation, please visit the official site HERE.

Severity

Low

Description

A warning intended to mark where the usage of CLUSTER BY may cause performance issues.

Code Example

Input Code:

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

Output Code:

CREATE TABLE PUBLIC.T_2008
(
COL1 NUMBER(20,0) NOT NULL,
COL2 INTEGER,
COL3 VARCHAR(4) COLLATE 'en-ci',
COL4 DATE
)
/*** MSC-WARNING - MSCEWI2008 - PERFORMANCE REVIEW - CLUSTER BY ***/
/*CLUSTER BY(PUBLIC.RANGE_N_UDF('RANGE_N(COL4 BETWEEN DATE \'2010-01-01\' AND DATE \'2025-12-31\' EACH INTERVAL \'1 year\')'), PUBLIC.CASE_N_UDF('CASE_N(COL3 = \'T\',COL3 = \'M\',COL3 = \'L\')'))*/;

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 [email protected]