Ataway Blog
We work where you work. With offices throughout Asia, Europe, North and South America, Ataway is your global business partner.


Content tagged 'Materialized View'

April 25, 2014
By Tao Zhang, Ataway Technical Consultant - Oracle Applications and Oracle Fusion Middleware

What is a Materialized View?

A Materialized View is a database object that contains the results of a query.  They can be added or dropped just like indexes without invalidating the SQL in the application code.

Materialized Views are similar to Regular Views in that they are a logical view of your data (based on a select statement). However, the underlying query result set has been saved to a physical table. So it only joins once, or as often as you refresh your materialized view, when querying this view.

One of the major benefits of Materialized View is its query optimization technique called “query re-write” that transforms a user query written in terms of tables and views into a faster query execution and data fetching from materialized views.  This feature is completely transparent to the end user, requiring no intervention or hints in the SQL application.  Although the SQL SELECT clause is a good candidate for Materialized View, statements such as INSERT or CREATE can also be candidates for Materialized View if they include the SELECT clause. 

Where are they most often used for?

Materialized Views are most often used in data warehousing / business intelligence applications to pre-compute and store aggregated data, such as the sum of sales.  In this case, the Materialized View eliminates the overhead associated with expensive joins and aggregations for a large or important class of queries.

Ataway Global Newsletter

Get valuable insights on the latest best practices involving ERP, process management, and local regulations worldwide, delivered right to your inbox!



Syndicate content