Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

I'm new to Oracle databases. If I have understood correctly, materialized view is a view which result set is saved as a physical table in the database and this view/table is refreshed bases on some parameter. If view is saved as a physical table, why not store the data into a table in the first place? So what is the benefit of using materialized view instead of a table?

share|improve this question
add comment

4 Answers 4

up vote 30 down vote accepted

One of the biggest benefit 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 detail table that produce 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.

share|improve this answer
add comment

Materialized Views are automatically updated as their base tables are updated.

share|improve this answer
add comment

One good case for using MVs is that some times you want to aggregate data and get this summary information from large tables frequently and quickly. Without materialized views, you have to either deonormalize some of your tables and maintain the aggregates via code or repeatedly scan large sets of rows. Either way is not always acceptable specially with dashboard and similar online applications. If you keep the results in a separate tables, you complicate your application code and as @Justin Cave says, you will be in charge of making sure that the manually aggregated data is in synch. with the original table's data.

share|improve this answer
add comment

Not an Oracle person, but another use case would be third party solutions. They generally do not support you making changes to their designs but a MV would be "invisible" to their code but provide access to custom reporting/data extracts.

It's not free in that it will cost have storage costs and potentially impactful insert/update time costs but that may be offset by the time spent retrieving the materialized data versus a "straight view" or creating actual tables and maintaining the surrounding ETL.

Finally, doing so may void your support contract with the vendor, consult-your-lawyer-blah-blah-blah

share|improve this answer
    
While this is a way to use a materialized view, the OP seems to be looking for its advantages over pure tables. –  Leigh Riffel Aug 28 '12 at 18:46
    
@LeighRiffel The advantage is you do not modify the base table. In my situation, it'd invalidate third party support. Furthermore, adding an extra column to a base table could cause queries to break if they are accessing based on ordinals. select * from a, b Add a column to table a and poorly written code is going to break. –  billinkc Aug 28 '12 at 19:14
    
I believe the separation of the derived table from the base table is already assumed, so the dilemma isn't between modifying the base or using a MV, but between using a derived table or a MV. –  Leigh Riffel Aug 28 '12 at 19:22
    
@LeighRiffel Ok, so then my second paragraph applies in that it keeps the data synced without paying the explicit cost of creating the derived table and writing code to keep that table in sync with the base. Much as others have already stated... –  billinkc Aug 28 '12 at 19:31
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.