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')
;

Note that the SELECT is performing multiple joins and accessing multiple tables. These are limitations of the Snowflake Materialized Views, so it will be transformed into a Snowflake regular view.

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

  1. MSCEWI1092: Materialized View was converted to regular View.

PreviousAlter TableNextCreate Database Link

Last updated