Create Materialized Views
In this section, you will find information about Oracle Materialized Views and their Snowflake equivalent.
Definition
A materialized view is a pre-computed data set derived from a query specification (the SELECT in the view definition) and stored for later use.
CREATE
The following is an Oracle's Materialized View create a basic example and its Snowflake counter part
Oracle
CREATE MATERIALIZED VIEW mv1
AS
SELECT
*
FROM
hr.employees
;
/
Snowflake
CREATE OR REPLACE MATERIALIZED VIEW PUBLIC.mv1
AS
SELECT
*
FROM
hr.employees;
ALTER
Altering the structure of a Materialized View in Snowflake is not supported, for these changes a CREATE OR REPLACE is recommended. There are only a handful of supported actions, as follows:
RENAME TO <NEW_NAME>, to rename the Materialized View
CLUSTER BY ( [, ... ] ), to cluster the Materialized View
DROP CLUSTERING KEY, to drop the cluster in the Materialized View
SUSPEND RECLUSTER, to suspend the clustering in the Materialized View
RESUME RECLUSTER, to resume in the Materialized View
SUSPEND, to suspend the maintenance of the Materialized View
RESUME, to resume the maintenance of the Materialized View
SET {SECURE | TAG = ' [ , <TAG_NAME = '' ... ] | COMMENT = '<COMMENT>' }
UNSET { SECURE | TAG = ' [ , <TAG_NAME = '' ... ] | COMMENT = '<COMMENT>' }
DROP
The following is an Oracle's Materialized View drop basic example and its Snowflake counterpart.
Oracle
DROP MATERIALIZED VIEW mv1;
Snowflake
DROP MATERIALIZED VIEW mv1;
Limitations
Oracle supports a large set of configurations for Materialized Views that Snowflake does not. The following list includes but is not limited to:
BUILD
CACHE / NOCACHE
CLUSTER
COMPRESS
DISTRIBUTE
EVALUATE
FOR UPDATE
INMEMORY | NO INMEMORY
LOB
LOGGING | NOLOGGING | FILESYSTEM_LIKE_LOGGING
MEMCOMPRESS
NESTED TABLE | STORE AS
ON PREBUILT TABLE
ORGANIZATION
PARALLEL | NOPARALLEL
PRIORITY REFRESH | NEVER REFRESH
SEGMENT CREATION
STORE AS [SECUREFILE | BASICFILE] LOB
USING INDEX | USING NO INDEX
VARRAY
WITH | WITHOUT
For more information about Snowflake's Materialized Views limitations, please use the following link Materialized Views Limitations
Transformation
Here we'll address the transformation process from Oracle to Snowflake. Following the limitations and considerations, when parsing and transforming an Oracle Materialized View is better to target a Snowflake's regular view, to keep consistency and prevent migration issues.
Simple Transformation
A simple transformation refers to a source code matching Snowflake's definition of Materialized View.
Oracle
CREATE MATERIALIZED VIEW simple_mv (column_1, column_2, column_3, column_4)
AS
SELECT
column_1
, column_2
, column_3
, column_4
FROM
sample_fact_table sft
WHERE
sft.column_4 >= TO_DATE('20210101','yyyymmdd')
;
Snowflake
CREATE OR REPLACE MATERIALIZED VIEW simple_mv (column_1, column_2, column_3, column_4)
AS
SELECT
column_1
, column_2
, column_3
, column_4
FROM
sample_fact_table sft
WHERE
sft.column_4 >= TO_DATE('20210101','yyyymmdd')
;
Complex Transformation
Complex transformation refers to a source code not matching Snowflake's definition of Materialized View. Any Oracle source code including any of the keywords in the Limitations section of this page or its SELECT contains any of the unsupported clauses present in the Snowflake documentation must be considered complex.
Oracle
CREATE MATERIALIZED VIEW complex_mv(column_1, column_2, column_3, column_4)
SEGMENT CREATION IMMEDIATE
ORGANIZATION HEAP PCTFREE 0 PCTUSED 40 INITRANS 1 MAXTRANS 255
COLUMN STORE COMPRESS FOR QUERY LOW NO ROW LEVEL LOCKING LOGGING
STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1
BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
TABLESPACE "USERS"
PARALLEL 6
BUILD IMMEDIATE
USING INDEX
REFRESH COMPLETE ON DEMAND
USING DEFAULT LOCAL ROLLBACK SEGMENT
USING ENFORCED CONSTRAINTS DISABLE QUERY REWRITE
AS
SELECT
column_1
, column_2
, column_3
, column_4
FROM
sample_fact_table sft
JOIN sample_dim_table1 sdt1
ON
sdt1.id = sft.sdt1_ID
LEFT OUTER JOIN sample_dim_table2 sdt2
ON
sdt2.id = sft.sdt2_ID
AND sdt2.description = "SAMPLE"
LEFT OUTER JOIN sample_dim_table3 sdt3
ON
sdt3.id = sft.sdt3_id
AND sdt3.description = "STORE"
WHERE
sft.column_4 >= TO_DATE('20210101','yyyymmdd')
;
Snowflake
/*** MSC-WARNING - MSCEWI1092 - MATERIALIZED VIEW WAS CONVERTED TO REGULAR VIEW. ***/
CREATE OR REPLACE VIEW PUBLIC.complex_mv (
column_1
, column_2
, column_3
, column_4
)
AS
SELECT
column_1
, column_2
, column_3
, column_4
FROM
PUBLIC.sample_fact_table sft
JOIN
PUBLIC.sample_dim_table1 sdt1
ON
sdt1.id = sft.sdt1_ID
LEFT OUTER JOIN
PUBLIC.sample_dim_table2 sdt2
ON
sdt2.id = sft.sdt2_ID
AND sdt2.description = "SAMPLE"
LEFT OUTER JOIN
PUBLIC.sample_dim_table3 sdt3
ON
sdt3.id = sft.sdt3_id
AND sdt3.description = "STORE"
WHERE
sft.column_4 >= TO_DATE('20210101','yyyymmdd');
Known issues
1. Materialized Views with subqueries are converted to regular Views.
Snowflake Materialized Views support subqueries if they meet certain specifications. This scenario is currently not recognized, every Materialized View that has a subquery is converted to a regular view. For more information regarding this case, check this documentation.
Related EWIs
MSCEWI1092: Materialized View was converted to regular View.
Last updated
Was this helpful?