Posts

Showing posts from August, 2010

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