Materialized Views in Netezza

Reading Time: 5 minutes

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.

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

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

Drop materialized views

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.

To create materialized view

CREATE MATERIALIZED VIEW CUSTOMER_DIM_MV
AS
SELECT
customer_key,
persona_key,
customer_birthdate,
customer_zip,
update_datetime
FROM
CUSTOMER_DIM
ORDER BY customer_key;


select
customer_key
, customer_birthdate
, customer_email
from (
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_date
from extract_base base
join CUSTOMER_DIM cd on base.customer_key = cd.customer_key
) a
where a.trans_date = 1;