What are Materialized Views
Oracle Materialized Views or MVs are a good option for data warehousing and replication. MVs based on inner/outer equijoin can be refreshed on-demand or periodically if desired. MVs that are based on sub-queries on remote tables support bi-directional replication. An MV includes a query that is transparent to the user, and includes a rewrite based on cost optimization. Oracle has a new object called Dimensions that supports the ability to rewrite a large class of queries.
MVs improve performance because expensive join and aggregation operations are pre-calculated before execution time. The optimizer is designed to automatically recognize when an MV can be used to satisfy a request to the database. The rewrite, as stated above, is transparent to the end-user. If everything is set up properly (error-free), the user should never know. Having the results cached beforehand greatly increases performance.
Why not use a table instead of a materialized view?
One of the biggest benefits of using a materialized view is that Oracle takes care of keeping the data in sync. If you have a separate aggregate table, you are responsible for keeping the data synchronized. That generally requires a reasonable amount of code and a decent amount of testing and most organizations manage to make mistakes that leave holes that cause the aggregate table to get out of sync. This is particularly true when you try to implement incremental refreshes of the aggregate table.
Another major benefit is that, depending on the settings, Oracle can use query rewrite to use materialized views when users issue queries against base tables. So, for example, if you have a bunch of existing reports against a detailed table that produces daily, monthly, and yearly aggregate results, you can create a materialized view on the base table that aggregates the data at a daily level and the optimizer can utilize that materialized view for all your existing queries. This makes it much easier to optimize reporting workloads in a data warehouse without trying to go and rewrite dozens of reports to use your new aggregate table or to mess with DBMS_ADVANCED_REWRITE to force your own rewrites of the queries.
When not to use Materialized Views?
There are cases when it is advised not to use Materialized Views. The most common ones are listed below:
- The Source Data is Simple and easy to Query.
- The source Data changes very quickly or can be accessed without using View. In such cases, we Creating Views should be avoided.
- Consistency is a high priority. The Views may not always be fully consistent with the original Data.