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 snowconvert-support@snowflake.com
Last updated