What is Materialized (Mat or Mview) view in Netezza and how is it different from normal views?

 

  What is Materialized (Mat or Mview) view in Netezza ?

Materialized views in Netezza are used to create a new table that contain subset of columns from the base tables and the data sorted by the columns in the new table. These kind of views are used to improve performance since it reduces the width of data being scanned since it creates a thin version of the base table and the data can be sorted in different order than the base table. Example usage of mat view is below:

create materialized view vendor_mview as select vendor_id, vendor_name from vendor order by vendor_id;



The limitation of materialized views in Netezza are:

1) Only one table can be used as the base (source) table.
2) There can be no where clause in the select statement and the columns used in the select must be part of the base table and no expressions can be used in the select statement.
3) Materialized (mat) views can be created from user tables only and cannot be temporary or external tables. 
4) You cannot GROOM a base table with an active materialized view created on top of it.
5) The materialized views needs maintenance since when data is added to the base table, the materialized views also get updated and data is added to the end in unsorted order. Hence, periodically the materialized (mview or mat) views need to be rebuilt.

The command to rebuild materialized view is:

ALTER VIEW <materialized_view_name> MATERIALIZE REFRESH 

How is materialized (mat) view different from normal views in Netezza?

The normal views in Netezza are used to select only required columns from multiple tables and make it look like a single table. Normal views do not create a real table and do not require maintenance when data is added to the base tables.

Normal views in Netezza can be based out of multiple tables, can contain where clause in the select statement, the columns can have expressions and pretty much everything you can have in a select statement. 

Sample create view statement is below:

create view vendor_mview as select vendor_id, vendor_name, product_id, product_name  from vendor, product where vendor.product_id=product.product_id and product_category='cell phone' order by vendor_id;

The normal views do not improve the performance of a query.The use of a normal view is more for convenience where instead of using the same select query multiple times, we can create a view instead and use the view in place of select statement. Also, the view permission can be changed and only few columns exposed to certain users who do not have permission to see all the columns of the base tables.

Check out, selecting distribution key in Netezza:
http://dwbitechguru.blogspot.ca/2014/12/selecting-distribution-key-in-netezza.html