Working with Materialized Views in Netezza

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 clumns to be referenced then you can create materialized view on top of base table by taking only those 10 columns.

Read :

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.

Materialized Views

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.

Benefits of Materialized views

Following are some of the benefits of Materialized views:

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

Syntax of Materialized View

Below is the Netezza materialized create view syntax:

CREATE MATERIALIZED VIEW <view_name> AS 
SELECT <column, column, …> FROM <base_table>
[ORDER BY <column, column, …>];

Create Materialized ViewYou can create the materizalized views from nzsql and nzadmin tool.

$ nzsql
TRAINING(admin)=>CREATE MATERIALIZED VIEW mv_test_mv AS
SELECT column1,column2 FROM table_name ORDER BY column1;

nzadmin tool

Materialized Views - nzadmin

Restriction on Materialized Views

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

Materialized Views: Best Practices

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 <%>

Materialized Views: Maintenance

Below are the some of maintainance 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;