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?
Why not use a table instead of a materialized view
oracleperformanceview
Related Question
- Running materialized view refresh in parallel
- Oracle – Materialized View Overloads Database
- Snapshot too old from materialized view refresh job
- Refresh materialize View fast on commit multiple table
- PostgreSQL – GROUP BY id Works on Table but Not on Identical View
- Oracle – Create Materialized View on Commit with PIVOT Function
- Oracle – Proper Procedure for Fixing Materialized View Replication
- Oracle – Forcing a Writable Materialized View Instead of an Updatable One
Best Answer
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.