3

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.

Improve this question

2 Answers 2

Reset to default
2

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

Improve this answer
Sign up to request clarification or add additional context in comments.

Comments

1

If you have two separate applications with some dependent data, then materialized view can help on data communication with security.

Let me give a real time example. Let say your application depends on external data, which can achieved by the following ways.

  1. Excel/xml file with a job run in every interval.
  2. Message Queue(MQ).
  3. Materialized view.

If you have the access to the Materialize view which takes your required data from the physical tables, then you don't need to worry about the synchronization, as Materialized view refreshes the data in a specific interval as your job does with excel/xml files. Also you don't need to worry about your data security as it is just a view.

Improve this answer

Comments

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.