Hello Friends,I came across a term named as materialized view and unable to understand that what is the use of materialized view? Please give me real time scenarios if anyone is familiar with it.
Thanks in Advance!
We use this for better performance and in my project client oltp data being imported as materialized views and loads it in evips, which is actual source(sql server) for we etl guys.
MV is a physical table. Unlike normal view(logical table), mv holds the data like tables.
We can create index, constraints on mv. Complex view codes can be written and hold those as in table using mv. Certainly it improves the performance but depends on the way it has been created.
MV is just to get updated data, when you are dealing with huge data.
There are some more benefits you can restrict user to view original data.If you are parent table is frequently getting updated the and by using mv you can trace the history by restricting with refresh time.
MV are nothing but replicas ,with refresh interval and physically stored in db.
MVs are like views, where data is taken from different base tables, sometimes from other schema also. But it differes from the normal views as MVs are updateable,/ DML operations can be done. The main advantage we see now is its help in improving the performance for long running queries. we could store the already filtered data in MV and this will help in retreiving the data faster. This is used in getting the monthly / quartely reports in the OLTP system.
But as nothing comes for free, the disadvantage is it takes a lot of time in refreshing the data and takes more space.
Can we do dml operations on materialized views?
Thanks for quick response.
Sign up for STechies
Ask Question From Our Experts