Sunday, October 19, 2014

What are Materialized View & Materialized View Logs?

A materialized view is a database object that contains the results of a query same like a table. They are local copies of data located on remote site. Also, used to create summary tables based on aggregations of a table's data, which store data based on remote tables are also, referred as Snapshots.

A materialized view can query tables, views, and other materialized views. Collectively these are called master tables (a replication term) or detail tables (a data warehouse term).

For replication purposes, materialized views allow you to maintain copies of remote data on your local server as read-only data.If we want to update the local copies of data, we have to use the Advanced Replication features in oracle. We can select data from a materialized view same as local table or view.

Materialized Views refer local data from Materialized View Logs (Snapshot Logs), which need to create on Local tables.

CREATE MATERIALIZED VIEW LOG ON emp;

Primary Key Materialized Views which are created with "WITH PRIMARY KEY" clause.

CREATE MATERIALIZED VIEW mv_emp_pk
REFRESH FAST START WITH SYSDATE 
NEXT  SYSDATE + 1/48
WITH PRIMARY KEY 
AS SELECT * FROM emp@remote;

Rowid Materialized Views which are created with "ROWID" clause.

CREATE MATERIALIZED VIEW mv_emp_rowid
REFRESH WITH ROWID 
AS SELECT * FROM emp@remote;

Subquery Materialized Views which are created by using subquery.

CREATE MATERIALIZED VIEW  mv_empdept
AS SELECT * FROM emp@remote e
WHERE EXISTS
     (SELECT * FROM dept@remote d
     WHERE e.deptid = d.deptid)

For data warehousing purposes, materialized views are the aggregate views, single-table aggregate views and join views.

CREATE MATERIALIZED VIEW  mv_empdept_dwh
AS SELECT * FROM emp@remote a, dept@remote b
WHERE a.deptid = b.deptid
and a.deptid < 1000;

COMPLETE refreshes by recalculating the defining query of the materialized view.

FAST refreshes by incrementally applying changes to the materialized view as per materialized view logs. For local materialized views, it chooses the refresh method which is estimated by optimizer to be most efficient.

FORCE Attempts a FAST refresh. If that is not possible, it does a complete refresh. For local materialized views, it chooses the refresh method which is estimated by optimizer to be most efficient. The refresh methods considered are log based FAST and COMPLETE.


NULLS LAST & NULLS FIRST in Select Statement.

In modern DBMS like Oracle, PostgreSQL, DB2, Firebird, Apache Derby, HSQLDB , H2, etc. you can specify NULLS LAST or NULLS FIRST in a select statement.

Used when you required sorted data in output having required column contains NULL values in it. It shows NULL values per below clauses.

NULLS LAST -  To sort data in which NULLS are showing in the last records.
NULLS FIRST - To sort data to the beginning - Default clause of a sql select statement.

select * from emp order by emp_name DESC NULLS LAST; 

select * from emp order by emp_name DESC NULLS FIRST;