1
0
During this post we will discuss about the difference between Regular View and Materialized View. We know in Regular view if query is run on the View, the reference is against the underlying base table. The output of the View query does not store on the Disk and every time it refers the original base tables. We can also called these View as Virtual table which is an output of Query statements.
Materialized views are design to improve query performance for workloads compose of common, repeated query patterns. Unlike Regular View, MV are stored on the disk and pre-computed data set derived from a query specification. Materialized views are automatically and transparently maintained by Snowflake. A background service updates the materialized view after changes are available to the base table.
Data access through MV is always current, regardless of the amount of DML that has perform on the base table.
If a query is run before the materialize view is up-to-date, Snowflake either updates the MV or uses the up-to-date portions of the view and retrieves any require newer data from the base table.
Limitation with MV:
Limitation with MV:
Supports query from one table and self joins with the same table does not allow.
Technical Implementation:
Create table and Insert data:
create or replace table ORDERS( ORDER_KEY NUMBER(38,0),CUST_KEY NUMBER(38,0),
ORDER_STATUS VARCHAR(1),TOTAL_PRICE NUMBER(38,0) ,ORDER_DATE DATE,
ORDER_PRIORITY VARCHAR(15),CLERK VARCHAR(15),SHIP_PRIORITY NUMBER(38,0),ORDER_COMMENT VARCHAR(80));
INSERT INTO ORDERS SELECT * FROM "SNOWFLAKE_SAMPLE_DATA"."TPCH_SF1000"."ORDERS";
Create Regular View and analyze the Query Profile:
create or replace View REGULAR_VIEW as
select ORDER_STATUS ORDER_STAT, ORDER_PRIORITY PRIORITY,SUM(TOTAL_PRICE) TOTAL_PRICE,COUNT(ORDER_KEY) ORDER_COUNT
from ORDERS group by ORDER_STATUS,ORDER_PRIORITY;
Generate Explain Plan for View:
EXPLAIN SELECT * FROM REGULAR_VIEW;
Create the Materialized View:
create or replace MATERIALIZED View MV_ORDER_VIEW as
select ORDER_STATUS ORDER_STAT, ORDER_PRIORITY PRIORITY,SUM(TOTAL_PRICE) TOTAL_PRICE,COUNT(ORDER_KEY) ORDER_COUNT
from ORDERS group by ORDER_STATUS,ORDER_PRIORITY;
Generate Explain Plan for MV:
EXPLAIN SELECT * FROM MV_ORDER_VIEW;
Deciding When to Create a Materialized View:
- Query results contain results that require significant processing, including:
- Aggregates that take a long time to calculate.
- The query is on an external table which may have slower performance compare to querying native database tables.
- The view’s base table does not change frequently.
Create a regular view when any of the following are true:
- The results of the view change often.
- The query is not resource intensive so it is not costly to re-run it.
