Create Index

In this section, you will find information about the transformation done to the Create Index in Oracle.

Example of a create index parsed code:

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

CREATE BITMAP INDEX COL1_INDEX ILM (ADD POLICY OPTIMIZE ( ON FUNC1 )) ON TABLE1 AS TAB1 (COL1 ASC) GLOBAL PARTITION BY RANGE (COL1, COL2) ( PARTITION VALUES LESS THAN (MAXVALUE) ) UNUSABLE IMMEDIATE INVALIDATION;

CREATE MULTIVALUE INDEX COL1_INDEX ILM (ADD POLICY SEGMENT TIER TO LOW_COST_TBS) ON TABLE1( TAB1 COL1 DESC, TAB1 COL2 ASC) FROM TABLE1 AS TAB1 WHERE COL1 > 0 LOCAL STORE IN (STORAGE1)
VISIBLE USABLE DEFERRED INVALIDATION;

CREATE INDEX COL1_INDEX ILM (DELETE POLICY POLICY1) ON CLUSTER CLUSTER1
PCTFREE 10
LOGGING
ONLINE
TABLESPACE DEFAULT
NOCOMPRESS
SORT
REVERSE
VISIBLE
INDEXING PARTIAL
NOPARALLEL;

CREATE INDEX COL1_INDEX ILM (DELETE_ALL) ON TABLE1 AS TAB1 (COL1 ASC) LOCAL (
PARTITION PARTITION1 TABLESPACE TABLESPACE1 NOCOMPRESS USABLE) DEFERRED INVALIDATION;

CREATE INDEX COL1_INDEX ON TABLE1 (COL1 ASC) GLOBAL
PARTITION BY HASH (COL1, COL2) (PARTITION PARTITION1 LOB(LOB1) STORE AS BASICFILE LOB_NAME (TABLESPACE TABLESPACE1)) USABLE IMMEDIATE INVALIDATION;

CREATE INDEX COL1_INDEX ON TABLE1 (COL1 DESC, COL2 ASC) INDEXTYPE IS INDEXTYPE1 LOCAL ( PARTITION PARTITION1 PARAMETERS('PARAMS')) NOPARALLEL PARAMETERS('PARAMS') USABLE DEFERRED INVALIDATION;

CREATE INDEX COL1_INDEX ON TABLE1 (COL1 ASC) INDEXTYPE IS XDB.XMLINDEX LOCAL ( PARTITION PARTITION1) PARALLEL 6 UNUSABLE IMMEDIATE INVALIDATION;

Due to architectural reasons, Snowflake does not support indexes so, SnowConvert will remove 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 an exceptionally 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.

Last updated