Saturday 18 June 2011

What’s the difference between View and Materialized View?

View:



Why Use Views?
1. To restrict data access
2. To make complex queries easy
3. To provide data independence
  A simple view is one that:
– Derives data from only one table
– Contains no functions or groups of data
– Can perform DML operations through the view.

 A complex view is one that:
– Derives data from many tables
– Contains functions or groups of data
– Does not always allow DML operations through the view.




A view has a logical existence but a materialized view has a physical existence. Moreover a materialized view can be Indexed, analysed and so on....that is all the things that  we can do with a table can also be done with a materialized view.
 
We can keep aggregated data into materialized view. we can schedule the MV to refresh but table can’t.MV can be created based on multiple tables.

Materialized View:

In DWH materialized views are very essential because in reporting side if we do aggregate calculations as per the business requirement report performance would be de graded. So to improve report performance rather than doing report calculations and joins at reporting side if we put same logic in the MV then we can directly select the data from MV without any joins and aggregations. We can also schedule MV (Materialize View).

No comments:

Post a Comment