This chapter discusses advanced topics in using materialized views. It contains the following topics:
6.1 About Partitioning and Materialized Views
Because of the large volume of data held in a data warehouse, partitioning is an extremely useful option when designing a database. Partitioning the fact tables improves scalability, simplifies system administration, and makes it possible to define local indexes that can be efficiently rebuilt. Partitioning the fact tables also improves the opportunity of fast refreshing the materialized view because this may enable partition change tracking (PCT) refresh on the materialized view. Partitioning a materialized view also has benefits for refresh, because the refresh procedure can then use parallel DML in more scenarios and PCT-based refresh can use truncate partition to efficiently maintain the materialized view.
Oracle Database VLDB and Partitioning Guide for further details about partitioning
This section contains the following topics:
6.1.1 About Partition Change Tracking
It is possible and advantageous to track freshness to a finer grain than the entire materialized view. You can achieve this through partition change tracking (PCT), which is a method to identify which rows in a materialized view are affected by a certain detail table partition. When one or more of the detail tables are partitioned, it may be possible to identify the specific rows in the materialized view that correspond to a modified detail partition(s); those rows become stale when a partition is modified while all other rows remain fresh.
You can use PCT to identify which materialized view rows correspond to a particular partition. PCT is also used to support fast refresh after partition maintenance operations on detail tables. For instance, if a detail table partition is truncated or dropped, the affected rows in the materialized view are identified and deleted.
Identifying which materialized view rows are fresh or stale, rather than considering the entire materialized view as stale, allows query rewrite to use those rows that are fresh while in
TRUSTED modes. Several views, such as
DBA_MVIEW_DETAIL_PARTITION, detail which partitions are stale or fresh. Oracle does not rewrite against partial stale materialized views if partition change tracking on the changed table is enabled by the presence of join dependent expressions in the materialized view.
"About Join Dependent Expression and Partition Change Tracking" for more information
Note that, while partition change tracking tracks the staleness on a partition and subpartition level (for composite partitioned tables), the level of granularity for PCT refresh is only the top-level partitioning strategy. Consequently, any change to data in one of the subpartitions of a composite partitioned-table will only mark the single impacted subpartition as stale and have the rest of the table available for rewrite, but the PCT refresh will refresh the whole partition that contains the impacted subpartition.
To support PCT, a materialized view must satisfy the following requirements:
At least one of the detail tables referenced by the materialized view must be partitioned.
Partitioned tables must use either range, list or composite partitioning with range or list as the top-level partitioning strategy.
The top level partition key must consist of only a single column.
The materialized view must contain either the partition key column or a partition marker or
ROWIDor join dependent expression of the detail table.
If you use a
BYclause, the partition key column or the partition marker or
ROWIDor join dependent expression must be present in the
If you use an analytic window function or the
MODELclause, the partition key column or the partition marker or
ROWIDor join dependent expression must be present in their respective
Data modifications can only occur on the partitioned table. If PCT refresh is being done for a table which has join dependent expression in the materialized view, then data modifications should not have occurred in any of the join dependent tables.
COMPATIBILITYinitialization parameter must be a minimum of 220.127.116.11.0.
PCT is not supported for a materialized view that refers to views, remote tables, or outer joins.
Oracle Database PL/SQL Packages and Types Reference for details regarding the
DBMS_MVIEW.PMARKER function and partition markers
This section contains the following topics:
18.104.22.168 About Partition Key and Partition Change Tracking
Partition change tracking requires sufficient information in the materialized view to be able to correlate a detail row in the source partitioned detail table to the corresponding materialized view row. This can be accomplished by including the detail table partition key columns in the
SELECT list and, if
BY is used, in the
Consider an example of a materialized view storing daily customer sales. The following example uses the
sh sample schema and the three detail tables
times to create the materialized view.
sales table is partitioned by
time_id column and
products is partitioned by the
times is not a partitioned table.
Example 6-1 Materialized View with Partition Key
CREATE MATERIALIZED VIEW LOG ON SALES WITH ROWID (prod_id, time_id, quantity_sold, amount_sold) INCLUDING NEW VALUES; CREATE MATERIALIZED VIEW LOG ON PRODUCTS WITH ROWID (prod_id, prod_name, prod_desc) INCLUDING NEW VALUES; CREATE MATERIALIZED VIEW LOG ON TIMES WITH ROWID (time_id, calendar_month_name, calendar_year) INCLUDING NEW VALUES; CREATE MATERIALIZED VIEW cust_dly_sales_mv BUILD DEFERRED REFRESH FAST ON DEMAND ENABLE QUERY REWRITE AS SELECT s.time_id, p.prod_id, p.prod_name, COUNT(*), SUM(s.quantity_sold), SUM(s.amount_sold), COUNT(s.quantity_sold), COUNT(s.amount_sold) FROM sales s, products p, times t WHERE s.time_id = t.time_id AND s.prod_id = p.prod_id GROUP BY s.time_id, p.prod_id, p.prod_name;
cust_dly_sales_mv, PCT is enabled on the
sales table because the partitioning key column
time_id is in the materialized view.
22.214.171.124 About Join Dependent Expression and Partition Change Tracking
An expression consisting of columns from tables directly or indirectly joined through equijoins to the partitioned detail table on the partitioning key and which is either a dimensional attribute or a dimension hierarchical parent of the joining key is called a join dependent expression. The set of tables in the path to detail table are called join dependent tables. Consider the following:
SELECT s.time_id, t.calendar_month_name FROM sales s, times t WHERE s.time_id = t.time_id;
In this query,
times table is a join dependent table because it is joined to
sales table on the partitioning key column
calendar_month_name is a dimension hierarchical attribute of
calendar_month_name is an attribute of
times.mon_id is a dimension hierarchical parent of
times.time_id. Hence, the expression
times tables is a join dependent expression. Let's consider another example:
SELECT s.time_id, y.calendar_year_name FROM sales s, times_d d, times_m m, times_y y WHERE s.time_id = d.time_id AND d.day_id = m.day_id AND m.mon_id = y.mon_id;
times table is denormalized into
times_y tables. The expression
times_y table is a join dependent expression and the tables
times_y are join dependent tables. This is because
times_y table is joined indirectly through
times_d tables to sales table on its partitioning key column
This lets users create materialized views containing aggregates on some level higher than the partitioning key of the detail table. Consider the following example of materialized view storing monthly customer sales.
Example 6-2 Creating a Materialized View: Join Dependent Expression
Assuming the presence of materialized view logs defined earlier, the materialized view can be created using the following DDL:
CREATE MATERIALIZED VIEW cust_mth_sales_mv BUILD DEFERRED REFRESH FAST ON DEMAND ENABLE QUERY REWRITE AS SELECT t.calendar_month_name, p.prod_id, p.prod_name, COUNT(*), SUM(s.quantity_sold), SUM(s.amount_sold), COUNT(s.quantity_sold), COUNT(s.amount_sold) FROM sales s, products p, times t WHERE s.time_id = t.time_id AND s.prod_id = p.prod_id GROUP BY t.calendar_month_name, p.prod_id, p.prod_name;
Here, you can correlate a detail table row to its corresponding materialized view row using the join dependent table
times and the relationship that
times.calendar_month_name is a dimensional attribute determined by
times.time_id. This enables partition change tracking on
sales table. In addition to this, PCT is enabled on products table because of presence of its partitioning key column
prod_id in the materialized view.
126.96.36.199 About Partition Markers and Partition Change Tracking
DBMS_MVIEW.PMARKER function is designed to significantly reduce the cardinality (the ratio of distinct values to the number of table rows) of the materialized view (see Example 6-3 for an example). The function returns a partition identifier that uniquely identifies the partition or subpartition for a specified row within a specified partitioned table. Therefore, the
DBMS_MVIEW.PMARKER function is used instead of the partition key column in the
Unlike the general case of a PL/SQL function in a materialized view, use of the
DBMS_MVIEW.PMARKER does not prevent rewrite with that materialized view even when the rewrite mode is
QUERY_REWRITE_INTEGRITY = ENFORCED.
As an example of using the
PMARKER function, consider calculating a typical number, such as revenue generated by a product category during a given year. If there were 1000 different products sold each month, it would result in 12,000 rows in the materialized view.
Example 6-3 Using Partition Markers in a Materialized View
Consider an example of a materialized view storing the yearly sales revenue for each product category. With approximately hundreds of different products in each product category, including the partitioning key column
prod_id of the
products table in the materialized view would substantially increase the cardinality. Instead, this materialized view uses the
DBMS_MVIEW.PMARKER function, which increases the cardinality of materialized view by a factor of the number of partitions in the
CREATE MATERIALIZED VIEW prod_yr_sales_mv BUILD DEFERRED REFRESH FAST ON DEMAND ENABLE QUERY REWRITE AS SELECT DBMS_MVIEW.PMARKER(p.rowid), p.prod_category, t.calendar_year, COUNT(*), SUM(s.amount_sold), SUM(s.quantity_sold), COUNT(s.amount_sold), COUNT(s.quantity_sold) FROM sales s, products p, times t WHERE s.time_id = t.time_id AND s.prod_id = p.prod_id GROUP BY DBMS_MVIEW.PMARKER (p.rowid), p.prod_category, t.calendar_year;
prod_yr_sales_mv includes the
DBMS_MVIEW.PMARKER function on the
products table in its
SELECT list. This enables partition change tracking on
products table with significantly less cardinality impact than grouping by the partition key column
prod_id. In this example, the desired level of aggregation for the
prod_yr_sales_mv is to group by
products.prod_category. Using the
DBMS_MVIEW.PMARKER function, the materialized view cardinality is increased only by a factor of the number of partitions in the
products table. This would generally be significantly less than the cardinality impact of including the partition key columns.
Note that partition change tracking is enabled on
sales table because of presence of join dependent expression
calendar_year in the
188.8.131.52 About Partial Rewrite in Partition Change Tracking
INSERT statement adds a new row to the
sales_part3 partition of table
sales. At this point, because
cust_dly_sales_mv has PCT available on table
sales using a partition key, Oracle can identify the stale rows in the materialized view
cust_dly_sales_mv corresponding to
sales_part3 partition (The other rows are unchanged in their freshness state). Query rewrite cannot identify the fresh portion of materialized views
prod_yr_sales_mv because PCT is available on table sales using join dependent expressions. Query rewrite can determine the fresh portion of a materialized view on changes to a detail table only if PCT is available on the detail table using a partition key or partition marker.
6.1.2 Partitioning a Materialized View
Partitioning a materialized view involves defining the materialized view with the standard Oracle partitioning clauses, as illustrated in the following example. This statement creates a materialized view called
part_sales_mv, which uses three partitions, can be fast refreshed, and is eligible for query rewrite:
CREATE MATERIALIZED VIEW part_sales_mv PARALLEL PARTITION BY RANGE (time_id) (PARTITION month1 VALUES LESS THAN (TO_DATE('31-12-1998', 'DD-MM-YYYY')) PCTFREE 0 STORAGE (INITIAL 8M) TABLESPACE sf1, PARTITION month2 VALUES LESS THAN (TO_DATE('31-12-1999', 'DD-MM-YYYY')) PCTFREE 0 STORAGE (INITIAL 8M) TABLESPACE sf2, PARTITION month3 VALUES LESS THAN (TO_DATE('31-12-2000', 'DD-MM-YYYY')) PCTFREE 0 STORAGE (INITIAL 8M) TABLESPACE sf3) BUILD DEFERRED REFRESH FAST ENABLE QUERY REWRITE AS SELECT s.cust_id, s.time_id, SUM(s.amount_sold) AS sum_dol_sales, SUM(s.quantity_sold) AS sum_unit_sales FROM sales s GROUP BY s.time_id, s.cust_id;
6.1.3 Partitioning a Prebuilt Table
Alternatively, a materialized view can be registered to a partitioned prebuilt table. "Benefits of Partitioning a Materialized View" describes the benefits of partitioning a prebuilt table. The following example illustrates this:
CREATE TABLE part_sales_tab_mv(time_id, cust_id, sum_dollar_sales, sum_unit_sale) PARALLEL PARTITION BY RANGE (time_id) (PARTITION month1 VALUES LESS THAN (TO_DATE('31-12-1998', 'DD-MM-YYYY')) PCTFREE 0 STORAGE (INITIAL 8M) TABLESPACE sf1, PARTITION month2 VALUES LESS THAN (TO_DATE('31-12-1999', 'DD-MM-YYYY')) PCTFREE 0 STORAGE (INITIAL 8M) TABLESPACE sf2, PARTITION month3 VALUES LESS THAN (TO_DATE('31-12-2000', 'DD-MM-YYYY')) PCTFREE 0 STORAGE (INITIAL 8M) TABLESPACE sf3) AS SELECT s.time_id, s.cust_id, SUM(s.amount_sold) AS sum_dollar_sales, SUM(s.quantity_sold) AS sum_unit_sales FROM sales s GROUP BY s.time_id, s.cust_id; CREATE MATERIALIZED VIEW part_sales_tab_mv ON PREBUILT TABLE ENABLE QUERY REWRITE AS SELECT s.time_id, s.cust_id, SUM(s.amount_sold) AS sum_dollar_sales, SUM(s.quantity_sold) AS sum_unit_sales FROM sales s GROUP BY s.time_id, s.cust_id;
In this example, the table
part_sales_tab_mv has been partitioned over three months and then the materialized view was registered to use the prebuilt table. This materialized view is eligible for query rewrite because the
REWRITE clause has been included.
184.108.40.206 Benefits of Partitioning a Materialized View
When a materialized view is partitioned on the partitioning key column or join dependent expressions of the detail table, it is more efficient to use a
PARTITION statement to remove one or more partitions of the materialized view during refresh and then repopulate the partition with new data. Oracle Database uses this variant of fast refresh (called PCT refresh) with partition truncation if the following conditions are satisfied in addition to other conditions described in "About Partition Change Tracking".
The materialized view is partitioned on the partitioning key column or join dependent expressions of the detail table.
If PCT is enabled using either the partitioning key column or join expressions, the materialized view should be range or list partitioned.
PCT refresh is nonatomic.
6.1.4 Rolling Materialized Views
When a data warehouse or data mart contains a time dimension, it is often desirable to archive the oldest information and then reuse the storage for new information. This is called the rolling window scenario. If the fact tables or materialized views include a time dimension and are horizontally partitioned by the time attribute, then management of rolling materialized views can be reduced to a few fast partition maintenance operations provided the unit of data that is rolled out equals, or is at least aligned with, the range partitions.
If you plan to have rolling materialized views in your data warehouse, you should determine how frequently you plan to perform partition maintenance operations, and you should plan to partition fact tables and materialized views to reduce the amount of system administration overhead required when old data is aged out. An additional consideration is that you might want to use data compression on your infrequently updated partitions.
You are not restricted to using range partitions. For example, a composite partition using both a time value and a key value could result in a good partition solution for your data.
Refreshing Materialized Views for further details regarding
FRESH and for details regarding compression
6.2 About Materialized Views in Analytic Processing Environments
This section discusses the concepts used by analytic SQL and how relational databases can handle these types of queries. It also illustrates the best approach for creating materialized views using a common scenario.
The following topics contain additional information about materialized views in different environments:
6.2.1 About Materialized Views and Analytic Views
Creating a materialized view over queries of an analytic view or a hierarchy is not supported.
6.2.2 About Materialized Views and Hierarchical Cubes
While data warehouse environments typically view data in the form of a star schema, for analytical SQL queries, data is held in the form of a hierarchical cube. A hierarchical cube includes the data aggregated along the rollup hierarchy of each of its dimensions and these aggregations are combined across dimensions. It includes the typical set of aggregations needed for business intelligence queries.
Example 6-4 Hierarchical Cube
Consider a sales data set with two dimensions, each of which has a four-level hierarchy:
Time, which contains (all times), year, quarter, and month.
Product, which contains (all products), division, brand, and item.
This means there are 16 aggregate groups in the hierarchical cube. This is because the four levels of time are multiplied by four levels of product to produce the cube. Table 6-1 shows the four levels of each dimension.
Table 6-1 ROLLUP By Time and Product
|ROLLUP By Time||ROLLUP By Product|
year, quarter, month
division, brand, item
Note that as you increase the number of dimensions and levels, the number of groups to calculate increases dramatically. This example involves 16 groups, but if you were to add just two more dimensions with the same number of levels, you would have 4 x 4 x 4 x 4 = 256 different groups. Also, consider that a similar increase in groups occurs if you have multiple hierarchies in your dimensions. For example, the time dimension might have an additional hierarchy of fiscal month rolling up to fiscal quarter and then fiscal year. Handling the explosion of groups has historically been the major challenge in data storage for online analytical processing systems.
Typical online analytical queries slice and dice different parts of the cube comparing aggregations from one level to aggregation from another level. For instance, a query might find sales of the grocery division for the month of January, 2002 and compare them with total sales of the grocery division for all of 2001.
6.2.3 Benefits of Partitioning Materialized Views
Materialized views with multiple aggregate groups give their best performance for refresh and query rewrite when partitioned appropriately.
PCT refresh in a rolling window scenario requires partitioning at the top level on some level from the time dimension. And, partition pruning for queries rewritten against this materialized view requires partitioning on
GROUPING_ID column. Hence, the most effective partitioning scheme for these materialized views is to use composite partitioning (range-list on (
GROUPING_ID) columns). By partitioning the materialized views this way, you enable:
PCT refresh, thereby improving refresh performance.
Partition pruning: only relevant aggregate groups are accessed, thereby greatly reducing the query processing cost.
If you do not want to use PCT refresh, you can just partition by list on
6.2.4 About Compressing Materialized Views
You should consider data compression when using highly redundant data, such as tables with many foreign keys. In particular, materialized views created with the
ROLLUP clause are likely candidates.
Oracle Database SQL Language Reference for data compression syntax and restrictions
"About Storage And Table Compression for Materialized Views" for details regarding compression
6.2.5 About Materialized Views with Set Operators
Oracle Database provides support for materialized views whose defining query involves set operators. Materialized views with set operators can now be created enabled for query rewrite. You can refresh the materialized view using either
Fast refresh is supported if the defining query has the
ALL operator at the top level and each query block in the
ALL, meets the requirements of a materialized view with aggregates or materialized view with joins only. Further, the materialized view must include a constant column (known as a
ALL marker) that has a distinct value in each query block, which, in the following example, is columns
1 marker and
"Restrictions on Fast Refresh on Materialized Views with UNION ALL" for detailed restrictions on fast refresh for materialized views with
220.127.116.11 Examples of Materialized Views Using UNION ALL
The following examples illustrate creation of fast refreshable materialized views involving
Example 6-5 Materialized View Using UNION ALL with Two Join Views
To create a
ALL materialized view with two join views, the materialized view logs must have the rowid column and, in the following example, the
ALL marker is the columns,
1 marker and
CREATE MATERIALIZED VIEW LOG ON sales WITH ROWID; CREATE MATERIALIZED VIEW LOG ON customers WITH ROWID; CREATE MATERIALIZED VIEW unionall_sales_cust_joins_mv REFRESH FAST ON COMMIT ENABLE QUERY REWRITE AS (SELECT c.rowid crid, s.rowid srid, c.cust_id, s.amount_sold, 1 marker FROM sales s, customers c WHERE s.cust_id = c.cust_id AND c.cust_last_name = 'Smith') UNION ALL (SELECT c.rowid crid, s.rowid srid, c.cust_id, s.amount_sold, 2 marker FROM sales s, customers c WHERE s.cust_id = c.cust_id AND c.cust_last_name = 'Brown');
Example 6-6 Materialized View Using UNION ALL with Joins and Aggregates
The following example shows a
ALL of a materialized view with joins and a materialized view with aggregates. A couple of things can be noted in this example. Nulls or constants can be used to ensure that the data types of the corresponding
SELECT list columns match. Also, the
ALL marker column can be a string literal, which is
'Quarter' umarker, or
'Daily' umarker in the following example:
CREATE MATERIALIZED VIEW LOG ON sales WITH ROWID, SEQUENCE (amount_sold, time_id) INCLUDING NEW VALUES; CREATE MATERIALIZED VIEW LOG ON times WITH ROWID, SEQUENCE (time_id, fiscal_year, fiscal_quarter_number, day_number_in_week) INCLUDING NEW VALUES; CREATE MATERIALIZED VIEW unionall_sales_mix_mv REFRESH FAST ON DEMAND AS (SELECT 'Year' umarker, NULL, NULL, t.fiscal_year, SUM(s.amount_sold) amt, COUNT(s.amount_sold), COUNT(*) FROM sales s, times t WHERE s.time_id = t.time_id GROUP BY t.fiscal_year) UNION ALL (SELECT 'Quarter' umarker, NULL, NULL, t.fiscal_quarter_number, SUM(s.amount_sold) amt, COUNT(s.amount_sold), COUNT(*) FROM sales s, times t WHERE s.time_id = t.time_id and t.fiscal_year = 2001 GROUP BY t.fiscal_quarter_number) UNION ALL (SELECT 'Daily' umarker, s.rowid rid, t.rowid rid2, t.day_number_in_week, s.amount_sold amt, 1, 1 FROM sales s, times t WHERE s.time_id = t.time_id AND t.time_id between '01-Jan-01' AND '01-Dec-31');
6.3 About Materialized Views and Models
Models, which provide array-based computations in SQL, can be used in materialized views. Because the
MODEL clause calculations can be expensive, you may want to use two separate materialized views: one for the model calculations and one for the
BY query. For example, instead of using one, long materialized view, you could create the following materialized views:
CREATE MATERIALIZED VIEW my_groupby_mv REFRESH FAST ENABLE QUERY REWRITE AS SELECT country_name country, prod_name prod, calendar_year year, SUM(amount_sold) sale, COUNT(amount_sold) cnt, COUNT(*) cntstr FROM sales, times, customers, countries, products WHERE sales.time_id = times.time_id AND sales.prod_id = products.prod_id AND sales.cust_id = customers.cust_id AND customers.country_id = countries.country_id GROUP BY country_name, prod_name, calendar_year; CREATE MATERIALIZED VIEW my_model_mv ENABLE QUERY REWRITE AS SELECT country, prod, year, sale, cnt FROM my_groupby_mv MODEL PARTITION BY(country) DIMENSION BY(prod, year) MEASURES(sale s) IGNORE NAV (s['Shorts', 2000] = 0.2 * AVG(s)[CV(), year BETWEEN 1996 AND 1999], s['Kids Pajama', 2000] = 0.5 * AVG(s)[CV(), year BETWEEN 1995 AND 1999], s['Boys Pajama', 2000] = 0.6 * AVG(s)[CV(), year BETWEEN 1994 AND 1999], ... <hundreds of other update rules>);
By using two materialized views, you can incrementally maintain the materialized view
my_groupby_mv. The materialized view
my_model_mv is on a much smaller data set because it is built on
my_groupby_mv and can be maintained by a complete refresh.
Materialized views with models can use complete refresh or PCT refresh only, and are available for partial text query rewrite only.
SQL for Modeling for further details about model calculations
6.4 About Security Issues with Materialized Views
To create a materialized view in your own schema, you must have the
VIEW privilege and the
READ privilege to any tables referenced that are in another schema. To create a materialized view in another schema, you must have the
VIEW privilege and the owner of the materialized view needs
READ privileges to the tables referenced if they are from another schema. Moreover, if you enable query rewrite on a materialized view that references tables outside your schema, you must have the
REWRITE privilege or the
REWRITE object privilege on each table outside your schema.
If the materialized view is on a prebuilt container, the creator, if different from the owner, must have the
READ WITH GRANT or
GRANT privilege on the container table.
If you continue to get a privilege error while trying to create a materialized view and you believe that all the required privileges have been granted, then the problem is most likely due to a privilege not being granted explicitly and trying to inherit the privilege from a role instead. The owner of the materialized view must have explicitly been granted
READ access to the referenced tables if the tables are in a different schema.
If the materialized view is being created with
REFRESH specified, then the owner of the materialized view requires an additional privilege if any of the tables in the defining query are outside the owner's schema. In that case, the owner requires the
REFRESH system privilege or the
REFRESH object privilege on each table outside the owner's schema.
6.4.1 Querying Materialized Views with Virtual Private Database (VPD)
For all security concerns, a materialized view serves as a view that happens to be materialized when you are directly querying the materialized view. When creating a view or materialized view, the owner must have the necessary permissions to access the underlying base relations of the view or materialized view that they are creating. With these permissions, the owner can publish a view or materialized view that other users can access, assuming they have been granted access to the view or materialized view.
Using materialized views with Virtual Private Database is similar. When you create a materialized view, there must not be any VPD policies in effect against the base relations of the materialized view for the owner of the materialized view. If any VPD policies exist, then you must use the
USING TRUSTED CONSTRAINTS clause when creating the materialized view. The owner of the materialized view may establish a VPD policy on the new materialized view. Users who access the materialized view are subject to the VPD policy on the materialized view. However, they are not additionally subject to the VPD policies of the underlying base relations of the materialized view, because security processing of the underlying base relations is performed against the owner of the materialized view.
This section contains the following topics:
18.104.22.168 Using Query Rewrite with Virtual Private Database
When you access a materialized view using query rewrite, the materialized view serves as an access structure much like an index. As such, the security implications for materialized views accessed in this way are much the same as for indexes: all security checks are performed against the relations specified in the request query. The index or materialized view is used to speed the performance of accessing the data, not provide any additional security checks. Thus, the presence of the index or materialized view presents no additional security checking.
This holds true when you are accessing a materialized view using query rewrite in the presence of VPD. The request query is subject to any VPD policies that are present against the relations specified in the query. Query rewrite may rewrite the query to use a materialize view instead of accessing the detail relations, but only if it can guarantee to deliver exactly the same rows as if the rewrite had not occurred. Specifically, query rewrite must retain and respect any VPD policies against the relations specified in the request query. However, any VPD policies against the materialized view itself do not have effect when the materialized view is accessed using query rewrite. This is because the data is already protected by the VPD policies against the relations in the request query.
22.214.171.124 Restrictions with Materialized Views and Virtual Private Database
Query rewrite does not use its full and partial text match modes with request queries that include relations with active VPD policies, but it does use general rewrite methods. This is because VPD transparently transforms the request query to affect the VPD policy. If query rewrite were to perform a text match transformation against a request query with a VPD policy, the effect would be to negate the VPD policy.
In addition, when you create or refresh a materialized view, the owner of the materialized view must not have any active VPD policies in effect against the base relations of the materialized view, or an error is returned. The materialized view owner must either have no such VPD policies, or any such policy must return
NULL. This is because VPD would transparently modify the defining query of the materialized view such that the set of rows contained by the materialized view would not match the set of rows indicated by the materialized view definition.
One way to work around this restriction yet still create a materialized view containing the desired VPD-specified subset of rows is to create the materialized view in a user account that has no active VPD policies against the detail relations of the materialized view. In addition, you can include a predicate in the
WHERE clause of the materialized view that embodies the effect of the VPD policy. When query rewrite attempts to rewrite a request query that has that VPD policy, it matches up the VPD-generated predicate on the request query with the predicate you directly specify when you create the materialized view.
6.5 Invalidating Materialized Views
Dependencies related to materialized views are automatically maintained to ensure correct operation. When a materialized view is created, the materialized view depends on the detail tables referenced in its definition. Any DML operation, such as an
UPDATE, or DDL operation on any dependency in the materialized view will cause it to become invalid. To revalidate a materialized view, use the
A materialized view is automatically revalidated when it is referenced. In many cases, the materialized view will be successfully and transparently revalidated. However, if a column has been dropped in a table referenced by a materialized view or the owner of the materialized view did not have one of the query rewrite privileges and that privilege has now been granted to the owner, you should use the following statement to revalidate the materialized view:
ALTER MATERIALIZED VIEW mview_name COMPILE;
The state of a materialized view can be checked by querying the data dictionary views
ALL_MVIEWS. The column
STALENESS will show one of the values
NEEDS_COMPILE to indicate whether the materialized view can be used. The state is maintained automatically. However, if the staleness of a materialized view is marked as
NEEDS_COMPILE, you could issue an
COMPILE statement to validate the materialized view and get the correct staleness state. If the state of a materialized view is
UNUSABLE, you must perform a complete refresh to bring the materialized view back to the
FRESH state. If the materialized view is based on a prebuilt table that you never refresh, you must drop and re-create the materialized view. The staleness of remote materialized views is not tracked. Thus, if you use remote materialized views for rewrite, they are considered to be trusted.
6.6 Altering Materialized Views
Change its refresh option (
Change its refresh mode (
Enable or disable its use for query rewrite.
Consider it fresh.
Partition maintenance operations.
Enable on-query computation
All other changes are achieved by dropping and then re-creating the materialized view. The success of a modification operation depends on whether the requirement for the change is satisfied. For example, a fast refresh succeeds if materialized view logs exist on all the base tables.
COMPILE clause of the
VIEW statement can be used when the materialized view has been invalidated. This compile process is quick, and allows the materialized view to be used by query rewrite again.
6.7 Using Real-time Materialized Views
Real-time materialized views provide fresh data to user queries even when the materialized view is marked as stale.
6.7.1 Overview of Real-time Materialized Views
A real-time materialized view is a type of materialized view that provides fresh data to user queries even when the materialized view is not in sync with its base tables because of data changes.
Unless a SQL session is set to stale tolerated mode, a materialized view that is marked stale cannot be used for query rewrite. Organizations that require real-time data typically use the
ON COMMIT refresh mode to ensure that the materialized view is updated with changes made to the base tables. However, when DML changes to the base tables are huge and very frequent, this mode may result in resource contention and reduced refresh performance. Real-time materialized views provide a lightweight solution for obtaining fresh data from stale materialized views by recomputing the data on the fly.
Real-time materialized views can use any available out-of-place refresh method including log-based or PCT based refresh. They can be used either with on demand or scheduled automatic refresh, but not with automatic refresh specified using the
ON COMMIT clause.
Advantages of Real-time Materialized Views
Provides improved availability for materialized views
Provides fresh data for user queries that access a materialized view that may be stale
How Do Real-time Materialized Views Work?
Real-time materialized views use a technique called on-query computation to provide fresh data with stale materialized views. When a query accesses a real-time materialized view, Oracle Database first checks if the real-time materialized view is marked as stale. If it is not stale, then the required data is provided using the real-time materialized view as it is. If the real-time materialized view is marked as stale, then the on-query computation technique is used to generate the fresh data and return the correct query result.
Real-time materialized views use a technique that is similar log-based refresh to provide fresh data with stale materialized view. They combine the existing data with the changes that are recorded in change logs to obtain the latest data. However, unlike log-based refresh, real-time materialized views do not use the materialized view logs to update the data in the real-time materialized view. Instead, when a query accesses a stale real-time materialized view, the data that is recomputed using on-query computation is used directly to answer the query.
A real-time materialized view is created by using the
ON QUERY COMPUTATION clause in the materialized view definition.
126.96.36.199 Restrictions on Using Real-time Materialized Views
Using real-time materialized views is subject to certain restrictions.
Real-time materialized views cannot be used when:
one or more materialized view logs created on the base tables are either unusable or nonexistent.
out-of-place, log-based or PCT refresh is not feasible for the change scenarios.
automatic refresh is specified using the
If a real-time materialized view is a nested materialized view that is defined on top of one or more base materialized views, then query rewrite occurs only if all the base materialized views are fresh. If one or more base materialized views are stale, then query rewrite is not performed using this real-time materialized view.
The cursors of queries that directly access real-time materialized views are not shared.
188.8.131.52 About Accessing Real-time Materialized Views
As with materialized views, multiple methods exist to access data stored in real-time materialized views.
Data stored in real-time materialized views can be accessed in one of the following ways:
A user query that is similar to the real-time materialized view definition is rewritten to use the real-time materialized view.
Direct access of real-time materialized views
A user query directly references the real-time materialized view by using its name.
In both scenarios, the content of a real-time materialized view can be accessed as stale data or can trigger an on-query computation of the correct result. Whether or not on-query computation is triggered depends on the environment and the actual SQL statement.
The output of the
EXPLAIN PLAN statement contains messages indicating if on-query computation was used for a particular user query.
6.7.2 Creating Real-time Materialized Views
To create a real-time materialized view, use the
ON QUERY COMPUTATION clause in the
CREATE MATERIALIZED VIEW statement.
You can create real-time materialized views even if they are not applicable for on-query computation for all change scenarios. The minimum requirement to create a real-time materialized view is that it supports out-of-place refresh for
INSERT operations. If other change scenarios, such as mixed DML operations, are encountered, then on-query computation may not be feasible for all types of real-time materialized views.
Real-time materialized view must use an out-of-place log-based refresh mechanism (including PCT refresh). The
ON COMMIT refresh mode cannot be used for real-time materialized views.
To create a real-time materialized view:
- Ensure that materialized view logs exist on all the base tables of the real-time materialized view.
- Create materialized view logs for all the tables on which the real-time materialized view is based.
- Create the real-time materialized view by including the
ENABLE ON QUERY COMPUTATIONclause in the
CREATE MATERIALIZED VIEWstatement.
Example 6-7 Creating a Real-time Materialized View
This example creates a real-time materialized view called
SUM_SALES_RTMV which is based on data aggregated from the
PRODUCTS tables in the
SH schema. Before you create the real-time materialized view ensure that the required prerequisites are met.
Create materialized view logs on the base tables
The following command creates a materialized view log on the
CREATE MATERIALIZED VIEW LOG ON sales WITH SEQUENCE, ROWID (prod_id, quantity_sold, amount_sold) INCLUDING NEW VALUES;
The following command creates a materialized view log on the
CREATE MATERIALIZED VIEW LOG ON products WITH ROWID (prod_id, prod_name, prod_category, prod_subcategory) INCLUDING NEW VALUES;
Create a real-time materialized view by including the
ON QUERY COMPUTATIONclause in the
CREATE MATERIALIZED VIEWstatement. The fast refresh method is used for this real-time materialized view and the
ENABLE QUERY REWRITEclause indicates that query rewrite must be enabled.
CREATE MATERIALIZED VIEW sum_sales_rtmv REFRESH FAST ON DEMAND ENABLE QUERY REWRITE ENABLE ON QUERY COMPUTATION AS SELECT prod_name, SUM(quantity_sold) AS sum_qty, COUNT(quantity_sold) AS cnt_qty, SUM(amount_sold) AS sum_amt, COUNT(amount_sold) AS cnt_amt, COUNT(*) AS cnt_star FROM sales, products WHERE sales.prod_id = products.prod_id GROUP BY prod_name;
SUM_SALES_RTMV real-time materialized view is created, assume that the following query is run.
SELECT prod_name, SUM(quantity_sold), SUM(amount_sold) FROM sales, products WHERE sales.prod_id = products.prod_id GROUP BY prod_name;
SUM_SALES_RTMV is not stale, then the query result is returned using the data stored in this real-time materialized view. However, if
SUM_SALES_RTMV is stale and the cost of rewriting the query using the materialized view with on-query computation is lower than the base table access, then the query is answered by combining the delta changes in the materialized view logs on the
PRODUCTS tables with the data in the real-time materialized view
6.7.3 Converting an Existing Materialized View into a Real-time Materialized View
If the prerequisites for a real-time materialized view are met, then an existing materialized view can be converted into a real-time materialized view by altering its definition and enabling on-query computation.
To convert a materialized view into a real-time materialized view:
- Modify the materialized view definition and enable on-query computation by using the
ON QUERY COMPUTATIONclause in the
ALTER MATERIALIZED VIEWstatement.
You can convert a real-time materialized view into a regular materialized view by disabling on-query computation using the
DISABLE ON QUERY COMPUTATION clause in the
ALTER MATERIALIZED VIEW statement.
Example 6-8 Converting a Materialized View into a Real-time Materialized View
The materialized view
SALES_RTMV is based on the
PRODUCTS tables and uses fast refresh. Materialized view logs exist on all three base tables. You want to modify this materialized view and convert it into a real-time materialized view.
Modify the materialized view definition and include the
ON QUERY COMPUTATIONclause to change it into a real-time materialized view.
ALTER MATERIALIZED VIEW sales_rtmv ENABLE ON QUERY COMPUTATION;
DBA_MVIEWSview to determine if on-query computation is enabled for
SELECT mview_name, on_query_computation
FROM dba_mviews WHERE mview_name = 'SALES_RTMV';
6.7.4 Enabling Query Rewrite to Use Real-time Materialized Views
For the query rewrite mechanism to rewrite a user query to use real-time materialized views, query rewrite must be enabled for the real-time materialized view.
You can enable query rewrite for a real-time materialized view either at creation time or subsequently, by modifying the definition of the real-time materialized view. The
ENABLE QUERY REWRITE clause is used to enable query rewrite.
To enable query rewrite for an existing real-time materialized view:
- Run the
ALTER MATERIALIZED VIEWcommand and include the
ENABLE QUERY REWRITEclause.
Example 6-9 Enabling Query Rewrite for Real-time Materialized Views
The real-time materialized view
my_rtmv uses the fast refresh mechanism. You want to modify the definition of this real-time materialized view and specify that the query rewrite mechanism must consider this real-time materialized view while rewriting queries.
The following command enables query rewrite for
ALTER MATERIALIZED VIEW my_rtmv ENABLE QUERY REWRITE;
6.7.5 Using Real-time Materialized Views During Query Rewrite
Query rewrite can use a real-time materialized view to provide results to user queries, even if the real-time materialized view is stale, if query rewrite is enabled for the real-time materialized view. A nested real-time materialized view is eligible for query rewrite only if all its base real-time materialized views are fresh.
When a user query is run, query rewrite first checks if a fresh materialized view is available to provide the required data. If a suitable materialized view does not exist, then query rewrite looks for a real-time materialized view that can be used to rewrite the user query. A fresh materialized view is preferred over a real-time materialized view because some overhead is incurred in computing fresh data for real-time materialized view. Next, the cost based optimizer determines the cost of the SQL query with on-query computation and then decides if the real-time materialized view will be used to answer this user query.
QUERY_REWRITE_INTEGRITY mode of the current SQL session is set to
STALE_TOLERATED, then on-query computation will not be used during query rewrite. The
STALE_TOLERATED rewrite mode indicates that fresh results are not required to satisfy a query, so on-query computation is not necessary.
For query rewrite to use a real-time materialized view:
- Ensure that
QUERY_REWRITE_INTEGRITYis set to either
QUERY_REWRITE_INTEGRITYmode should not be set to
- Run a user query that matches the SQL query that was used to define the real-time materialized view.
Any query that can be rewritten to take advantage of a real-time materialized view will use the real-time materialized view with on-query computation.
EXPLAIN PLANto verify that the query was rewritten using the real-time materialized view.
Example 6-10 Using Real-time Materialized Views During Query Rewrite
This example creates a real-time materialized view with query rewrite enabled and then demonstrates that it was used by query rewrite to provide data for a user query.
Create a materialized view log on the
SALEStable, which is the base table for the real-time materialized view being created.
Create a real-time materialized view
mav_sum_saleswith query rewrite enabled.
CREATE MATERIALIZED VIEW mav_sum_sales REFRESH FAST ON DEMAND ENABLE ON QUERY COMPUTATION ENABLE QUERY REWRITE AS SELECT prod_id, sum(quantity_sold) as sum_qty, count(quantity_sold) as cnt_qty, sum(amount_sold) sum_amt, count(amount_sold) cnt_amt, count(*) as cnt_star FROM sales GROUP BY prod_id;
Run the following query:
SELECT prod_id, sum(quantity_sold), sum(amount_sold) FROM sales WHERE prod_id < 1000 GROUP BY prod_id;
Observe that the query is similar to the one used to define the real-time materialized view
mav_sum_sales. Because no other materialized view with a definition that is similar to the query exists, query rewrite can use the
mav_sum_salesreal-time materialized view to determine the query result. You can verify that query rewrite has taken place by checking the SQL cursor cache (for example, with
DBMS_XPLAN), using SQL Monitor, or using
The internally rewritten query that uses
mav_sum_salesis analogous to the following statement:
SELECT prod_id, sum_qty, sum_amt FROM mav_sum_sales WHERE prod_id < 1000;
Verify that the real-time materialized view was used to provide the query result. Use the
EXPLAIN PLANstatement to view the execution plan for the query.
The following execution plan shows direct access to the real-time materialized view. If the materialized view is stale, then the execution plan will become more complex and include access to other objects (for example, the materialized view logs), depending on the outstanding DML operations.
EXPLAIN PLAN for SELECT prod_id, sum(quantity_sold), sum(amount_sold) FROM sales WHERE prod_id < 1000 GROUP BY prod_id; SELECT plan_table_output FROM table(dbms_xplan.display('plan_table',null,'serial')); PLAN_TABLE_OUTPUT ----------------------------------------------------------------------------------------------- Plan hash value: 13616844 ----------------------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU) | Time | ----------------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 92 | 3588 | 3 (0) | 00:00:01 | | *1 | MAT_VIEW ACCESS FULL | MAV_SUM_SALES | 92 | 3588 | 3 (0) | 00:00:01 | ----------------------------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 1 - filter("PROD_ID"<1000) Note ----- - dynamic statistics used: dynamic sampling (level=2) 17 rows selected.
6.7.6 Using Real-time Materialized Views for Direct Query Access
You can access a real-time materialized view directly by referencing the name of the real-time materialized view in a query.
If the real-time materialized view specified in a user query is fresh, then the required data is directly fetched from the real-time materialized view. If the real-time materialized view is stale, then you must use the
FRESH_MV hint to perform on-query computation and obtain fresh data. Oracle Database does not automatically perform on-query computation for a real-time materialized view that is accessed directly in a user query.
To obtain fresh data from a stale real-time materialized view when directly accessing the real-time materialized view:
- Use the
FRESH_MVhint in the user query to indicate that on-query computation must be performed.
Example 6-11 Creating a Real-Time Materialized View and Using it in Queries
This example creates a real-time materialized view
MY_RTMV that is based on the
SALES_NEW table. The
SALES_NEW table is created as a copy of the
SH.SALES table. A row is inserted into the base table after the real-time materialized view is created. Next the
fresh_mv hint is used to access fresh data from the real-time materialized view by using the materialized view name in a user query.
Create a materialized view log on the base table
Materialized view logs on the base table are mandatory for creating real-time materialized views.
CREATE MATERIALIZED VIEW LOG on sales_new WITH sequence, ROWID (prod_id, cust_id, time_id, channel_id, promo_id, quantity_sold, amount_sold) INCLUDING NEW VALUES;
Create a real-time materialized view called
sales_newas the base table.
ON QUERY COMPUTATIONclause indicates that a real-time materialized view is created. The refresh mode specified is log-based fast refresh. Query rewrite is enabled for the real-time materialized view.
CREATE MATERIALIZED VIEW my_rtmv REFRESH FAST ENABLE ON QUERY COMPUTATION ENABLE QUERY REWRITE AS SELECT prod_id, cust_id, channel_id, sum(quantity_sold) sum_q, count(quantity_sold) cnt_q, avg(quantity_sold) avg_q, sum(amount_sold) sum_a, count(amount_sold) cnt_a, avg(amount_sold) avg_a FROM sales_new GROUP BY prod_id, cust_id, channel_id;
Insert a row into
sales_new, the base table of the real-time materialized view and commit this change.
INSERT INTO sales_new (prod_id, cust_id, time_id, channel_id, promo_id, quantity_sold, amount_sold) VALUES (116,100450, sysdate,9,9999,10,350); COMMIT;
Query the real-time materialized view directly to display data for the row that was added to the real-time materialized view’s base table in the previous step.
SELECT * from my_rtmv WHERE prod_id = 116 AND cust_id=100450 AND channel_id = 9; PROD_ID CUST_ID CHANNEL_ID SUM_Q CNT_Q AVG_Q SUM_A CNT_A AVG_A ------- ------- ---------- ----- ----- ----- ----- ----- ------ 116 100450 9 1 1 1 11.99 1 11.99
Note that the query result does not display the updated value for this data. This is because the real-time materialized view has not yet been refreshed with the changes made to its base table.
FRESH_MVhint while querying the real-time materialized view to display the row updated in the base table.
SELECT /*+ fresh_mv */ * FROM my_rtmv WHERE prod_id = 116 AND cust_id=100450 AND channel_id = 9; PROD_ID CUST_ID CHANNEL_ID SUM_Q CNT_Q AVG_Q SUM_A CNT_A AVG_A ------- ------- ---------- ----- ----- ----- ----- ----- ------ 116 100450 9 11 2 5.5 361.99 2 180.995
Notice that this time the updated row is displayed. This is because the
FRESH_MVhint triggers on-query computation for the real-time materialized view and recomputed the fresh data.
6.7.7 Listing Real-time Materialized Views
ON_QUERY_COMPUTATION column in the data dictionary views
USER_MVIEWS indicates if a materialized view is a real-time materialized view.
A value of Y in the
ON_QUERY_COMPUTATION column indicates a real-time materialized view.
- Query the
USER_MVIEWSview and display details of the materialized view with the
ON_QUERY_COMPUTATIONcolumn set to Y.
Example 6-12 Listing Real-time Materialized Views in the Current User’s Schema
SELECT owner, mview_name, rewrite_enabled, staleness FROM user_mviews WHERE on_query_computation = 'Y'; OWNER MVIEW_NAME REWRITE_ENABLED STALENESS ------ ------------ ------------------- ------------ SH SALES_RTMV N FRESH SH MAV_SUM_SALES Y FRESH SH MY_SUM_SALES_RTMV Y FRESH SH NEW_SALES_RTMV Y STALE
6.7.8 Improving Real-time Materialized Views Performance
To obtain better performance for user queries that use a real-time materialized view, you can follow certain guidelines.
Use the following guidelines with real-time materialized views:
Frequently refresh real-time materialized views to enhance the performance of queries that may use these real-time materialized views.
Since real-time materialized views work by combining the delta changes to the base tables with the existing materialized view data, query response time is enhanced when the delta changes to be computed are small. With more outstanding DML operations, on-query computation can become more complex (and expensive), up to the point where direct base table access can become more efficient (in case of query rewrite).
Collect statistics for the base tables, the real-time materialized view, and the materialized view logs to enable the optimizer to accurately determine the cost of a query.
For query rewrite, the cost-based rewrite mechanism uses the optimizer to determine whether the rewritten query should be used. The optimizer uses statistics to determine the cost.