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.

Oracle

Query

IN -> Oracle_01.sql

CREATE MATERIALIZED VIEW mv1
AS 
    SELECT 
        * 
    FROM 
        hr.employees
    ;
/

Snowflake

Query

OUT -> Oracle_01.sql

!!!RESOLVE EWI!!! /*** SSC-EWI-0092 - MATERIALIZED VIEW WAS CONVERTED TO REGULAR VIEW. ***/!!!
CREATE OR REPLACE VIEW mv1
COMMENT = '{"origin":"sf_sc","name":"snowconvert","version":{"major":1, "minor":0},{"attributes":{"component":"oracle"}}'
AS
--** SSC-FDM-0001 - VIEWS SELECTING ALL COLUMNS FROM A SINGLE TABLE ARE NOT REQUIRED IN SNOWFLAKE AND MAY IMPACT PERFORMANCE. **
SELECT
    *
FROM
    hr.employees;

Related EWIs

1. SSC-EWI-0092: MATERIALIZED VIEW WAS CONVERTED TO REGULAR VIEW

2. SSC-FDM-0001: Views selecting all columns from a single table are not required in Snowflake