Links

MSCEWI1091

Create Index Not Supported

Severity

Low

Description

Due to architectural reasons, Snowflake does not support indexes so, SnowConvert will comment out all the code related to the creation of indexes. Snowflake automatically creates micro-partitions for every table that help speed up the performance of DML operations, the user does not have to worry about creating or managing these micro-partitions.
Usually, this is enough to have a very good query performance however, there are ways to improve it by creating data clustering keys. Snowflake's official page provides more information about micro-partitions and data clustering.

Code Example

Input Code:

CREATE UNIQUE INDEX COL1_INDEX ILM (ADD POLICY OPTIMIZE AFTER 10 DAYS OF NO ACCESS) ON CLUSTER CLUSTER1
ONLINE USABLE DEFERRED INVALIDATION;

Output Code:

-- ** MSC-WARNING - MSCEWI1091 - CREATE INDEX NOT SUPPORTED. **
---- CREATE INDEX
--CREATE UNIQUE INDEX COL1_INDEX ILM (ADD POLICY OPTIMIZE AFTER 10 DAYS OF NO ACCESS) ON CLUSTER CLUSTER1
--ONLINE USABLE DEFERRED INVALIDATION

Recommendations

  • Data clustering might be a way to speed up query performance on tables.
  • For more support, you can email us at [email protected] or post a message to our forums. If you have a contract for support with Mobilize.Net, reach out to your sales engineer and they can direct your support needs.