Materialized View is a sorted projection of one and only one table MVs reduce the width of the data being scanned by creating a thin version of the table–based on frequently queried columns. This selection of frequently queried columns is called projection.

MVs are typically sorted like the base tables, but can be ordered on columns different from base table. This reduces table scans due to enhanced zone maps. and it improves query performance if the sorted columns are included in the “where” clause of the query.  Essentially, MVs improve performance by reducing the amount of data transferred from the disk to the CPU-RAM.

Materialized view will always has same distribution key as base table. Materialized view inherits the insertion order from base table if the order by is not specified. New rows that are inserted to to the base table are appended to end of MV.

The materialized views containing the sorted projection (columns) is stored in a table on disk and is used to increase query performance. A materialized views reduces scan time for multi-column queries that examine only a few columns and a small subset of the overall base table.

• User defined
• Simple to create
• Netezza MV are automatically managed
• If there are multiple MV’s created on same base table then optimizer determines when and which materialized views to use
• Reflected in the Netezza query plan
• Automatically maintained

Like any other databases, Netezza also has the some restrictions on the Materialized views:

• One cannot insert, update, delete or truncate a materialized view
• You must specify at least one column in projection list
• You can specify only one base table in from clause
• one cannot specify NULLS LAST or DESC in the ORDER BY clause

• Expressions are not allowed as a column
• Base table cannot be a external table, system table or temp tables
• You cannot use WHERE clause in a materialized view
• You cannot specify clustered base table (CBT) as base table

Below are the some of the best practices you should follow:

• Create very thin MATERIALIZED VIEW: Use as few columns as possible
• Create MATERIALIZED VIEW on columns that are frequently queried together
• Put ORDER BY clause on restrictive columns. i.e. columns used in WHERE condition
• Create few MATERIALIZED VIEW for each table
• Periodically or manually REFRESH the MATERIALIZED VIEWS
• Set an acceptable threshold percentage of unsorted data in a MATERIALIZED VIEW – SET SYSTEM DEFAULT MATERIALIZE [REFRESH] THRESHOLD <%>

Below are the some of maintenance tips:

• Automatically maintained on LOAD, INSERT, UPDATE, DELETE and TRUNCATE TABLE: Performance of queries that use a MATERIALIZED VIEW may degrade over time as unsorted rows are appended to the materialized view as a result of LOAD, INSERT and UPDATE operations
• For large modifications (LOAD, INSERT, DELETE) to a given table, it is recommended to SUSPEND a MATERIALIZED VIEW before the batch operation and REFRESH after the completion of the batch operation
• ALTER VIEWS ON <table> MATERIALIZE {SUSPEND|REFRESH}: Command to SUSPEND / REFRESH a MATERIALIZED VIEW

e.g.

$nzsql TRAINING(admin)=>ALTER VIEWS ON customer MATERIALIZE SUSPEND;

To rebuild a view after a base table change, use the CREATE OR REPLACEMATERIALIZED VIEW command to update the view, as follows:

MYDB.SCHEMA(ADMIN)=>CREATE OR REPLACE MATERIALIZED VIEW weather_v AS SELECTcity, temp_lo, temp_hi FROM weather ORDER BY city;CREATE MATERIALIZED VIEW

Do not drop and re-create the materialized view because those steps result in anew view with a different object ID, which can affect other objects that reference the materialized view.

When you use SQL to drop a materialized view, the system removes the view definition, the materialized table that contains the materialized records, and frees the disk storage that is allocated to the table. To drop a materialized view, enter:

MYDB.SCHEMA(USER)=>DROP VIEW customers_mview;

When you create a materialized views from a base table, the Netezza system stores the view definition for the lifetime of the SPM view and is visible as a materialized view. SPM view data slices are co-located on the same data slices as the corresponding base table data slices hence increases the performance of the query.

A materialized views reduces the width of number of columns being scanned in a base table, these type of view contains a small subset of frequently queried columns. When you query the table (table with large number of columns) Materialized Views  avoid scanning unreferenced columns from the base tables.

E.g. If the base table has 300 columns, and only 4 columns to be referenced then you can create materialized view on top of base table by taking only those 10 columns.

CREATE MATERIALIZED VIEW CUSTOMER_DIM_MVASSELECTcustomer_key,persona_key,customer_birthdate,customer_zip,update_datetimeFROMCUSTOMER_DIMORDER BY customer_key;

selectcustomer_key, customer_birthdate, customer_emailfrom (select base.customer_key,cd.customer_birthdate,cd.customer_email,row_number() over (partition by base.customer_key order by cd.update_datetime desc) trans_datefrom extract_base basejoin CUSTOMER_DIM cd on base.customer_key = cd.customer_key) awhere a.trans_date = 1;

Meet Ananth Tirumanur. Hi there 👋

I work on projects in data science, big data, data engineering, data modeling, software engineering, and system design.

• 👨‍💻 All of my projects are available at https://github.com/akrish1982
• 💬 Ask me about Data engineering, SQL, Databases, Data pipelines, Data infrastructure
• 📄 My work history: https://www.linkedin.com/in/ananthtirumanur/
• ⚡ Fun fact: Marathoner & Casual Birding enthusiast

Connect with me:

• Twitter: @akrish82
• LinkedIn: https://linkedin.com/in/ananthtirumanur/

My Resources:

• LinkedIn Newsletter: Data Engineering with AWS
• Udemy Course: AWS Certified Data Engineer Associate Practice Test
• Python Crash Course: Python Crash Course on Gumroad

Languages and Tools:

AWS, Bash, Docker, Elasticsearch, Git, Grafana, Hadoop, Hive, EMR, Glue, Athena, Lambda, Step Functions, Airflow/MWAA, DynamoDB, Kafka, Kubernetes, Linux, MariaDB, MySQL, Pandas, PostgreSQL, Python, Redis, Scala, SQLite