materialized view use case

Question

I have been learning about materialized views and for what they are used/can be used.

However i dont find how it would be useful , since it doesnt contain current data because it takes time to refresh/update. Also looking at real life scenarios it is used for a.)Ease Network Loads and b.)Enable Data Subsetting among others as per oracle docs. Now if i have localised servers for reducing load , i can create a localised databse itself which will cater to local data for eg country specific google servers.

If anybody could explain why materialized views are used in real life situations.

Answer 1

why materialized views are used in real life situations.

There are many reasons, and it depends on the system, application, requirement etc. If you want to automatically keep the data in sync against the base tables, then you need not write your own code for that. Oracle automatically does it for you.

Another thing is query rewrite. If it is enabled, optimizer could make use of the materialized view for the queries.

Useful in,

1. Data Warehouses
2. Distributed Computing
3. Mobile Computing

From the docs,

The Need for Materialized Views

You can use materialized views to increase the speed of queries on very large databases. Queries to large databases often involve joins between tables, aggregations such as SUM, or both. These operations are expensive in terms of time and processing power. The type of materialized view you create determines how the materialized view is refreshed and used by query rewrite.

Materialized views improve query performance by precalculating expensive join and aggregation operations on the database prior to execution and storing the results in the database. The query optimizer automatically recognizes when an existing materialized view can and should be used to satisfy a request. It then transparently rewrites the request to use the materialized view. Queries go directly to the materialized view and not to the underlying detail tables. In general, rewriting queries to use materialized views rather than detail tables improves response time.

A similar question was asked here https://dba.stackexchange.com/questions/23280/why-not-use-a-table-instead-of-a-materialized-view