Oracle Vs MS-SQL - General facts about Materialized Views

In the previous post we had discussed about the features and use of views. Now it is the time to discover the use of materialized views. It stores the data which is coming from the view as a table. This is most useful in the data warehousing field. Materialized views improve the performance not only because of the presence of index but also below mentioned points.


• Aggregations can be pre computed and stored in the index to minimize expensive computations during query execution.
• Tables are pre joined and results get stored
• Combination of tables and aggregation can be stored

We should carefully decide to go for materialized view or not because it will take more overhead on the memory and also maintenance. Consider the below points

• The query which are having more joins
• More aggregate results
• As a normal index on table, this also gives performance improvement only if we make use of the index

We cannot have self join in materialized view in MS-Sql as well as in Oracle.
 
I will be writing about the differences of materialized view in Oracle and in MS-SQL in my next post.

Comments

Popular posts from this blog

Hadoop - Hive - Load data from csv/xls files

Microsoft BI Implementation - Cube back up and restore using XMLA command