When using query rewrite, create materialized views that satisfy the largest number of queries. Overview of Materialized View Management Tasksĭescription of 'Figure 5-1 Transparent Query Rewrite' This section contains the following topics:Ībout Materialized Views for Data WarehousesĪbout Materialized Views for Distributed ComputingĪbout Materialized Views for Mobile Computing

However, serious consideration should be given to whether users should be allowed to do this because any change to the materialized views affects the queries that reference them.

Materialized views within the data warehouse are transparent to the end user or to the database application.Īlthough materialized views are usually accessed through the query rewrite mechanism, an end user or database application can construct queries that directly access the materialized views. This mechanism reduces response time for returning results from the query. The query rewrite mechanism in the Oracle server automatically rewrites the SQL query to use the summary tables.

The end user queries the tables and views at the detail data level. The database administrator creates one or more materialized views, which are the equivalent of a summary. Materialized views can perform a number of roles, such as improving query performance or providing replicated data. The summaries or aggregates that are referred to in this book and in literature on data warehousing are created in Oracle Database using a schema object called a materialized view. For example, you can create a summary table to contain the sums of sales by region and by product. Summaries are special types of aggregate views that improve query execution times by precalculating expensive joins and aggregation operations prior to execution and storing the results in a table in the database. One technique employed in data warehouses to improve performance is the creation of summaries. Usually, the vast majority of the data is stored in a few very large fact tables. Data warehouses commonly range in size from hundreds of gigabytes to a few terabytes. The data is normally processed in a staging file before being added to the data warehouse. Typically, data flows from one or more online transaction processing (OLTP) database into a data warehouse on a monthly, weekly, or daily basis.

Download tableplus 5.3.3