Oracle 12cR2 introduced a new feature called Real-time Materialized View. This feature improves the availability of MV in data warehouse applications.
What is Oracle Materialized View (MV)
Typically, a data warehouse receives detailed data from transactions system or other sources. Database will need to perform calculation when analyst queries summary data, or joined data. A technique to improve performance is to store the summary or joined data in database so that the calculation can be reused across queries.
Oracle Materialized View feature employees 2 functionalities: stores summary data in Materialized View as a physical table; re-write query use Materialized View when logically possible even the SQL does not reference the materialized view (Transparent Query Rewrite)
Real-time Materialized View improves MV availability
When the base tables get updated, Materialized View needs to be refreshed to maintain the data accuracy. Oracle provide choice on when to update the materialized view.
1. ON DEMAND – manual refresh, MV is refreshed on demand
2. ON COMMIT – when transaction on base tables are commited
3. ON STATEMENT (12c new feature) – when DML is running on base tables even before commit
There are 2 challenges that can make Materialized View unusable.
1. when materialized view is not refreshed while the data in base tables changed, the MV is marked as “Stale”. Queries will not be able to use the staled MV, or the query will return the inaccurate data (alter session set query_rewrite_integrity = stale_tolerated; )
2. The materialized view is unusable when it's being refreshed
The real-time materialized view improves MV availability by allowing query to get accurate data even before MV is up to date. Oracle can keep the base table data change some where outside of base table (out-of-place). Real-time materialized view provide the accurate result by combining the data in MV and out-of-place data.
Example of creating Real-time Materialized View:
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;
Using Oracle Materialized View in Strategy
Strategy is an aggregate aware application that allows end users to query physical summary tables without needing to specify which table to use to satisfy the query. Oracle's Materialized Views is a database server feature that provides for the creation of, maintenance of, and automatic navigation to aggregate tables. This makes the use of aggregate tables entirely transparent to the end user and to the Strategy developer. With Materialized Views, Oracle will rewrite the query to use the summary table, provided that it determines its cost to be less than the original query. Query rewrite in Oracle is based on the SQL text itself or on rewrite conditions explicitly determined by defining dimensions in Oracle. Any Strategy generated SQL could make use of query rewrite and materialized views.
However, in many cases making the aggregate table aware to Strategy as physical summary table give further benefit to users. Strategy can take advantage of its knowledge of hierarchical relationships to select from physical summary tables ; with materialized views in Oracle, this information must be redefined via hierarchical links. For example, within a Time hierarchy having Day, Week, Month, and Year attribute levels; direct mappings between Year to Month, Year to Week, Year to Day, Month to Week, Month to Day, and Week to Day need to be made. The Oracle optimizer cannot infer a relationship between Year to Week from relationships between Year to Month and Month to Week. Thus materialized view links need to be created for every possible combination of pre-aggregated material view level to levels where a physical table exists.
Additionally, there are some cases where Oracle query rewrite will not access materialized views and will miss opportunities to access pre-aggregated data where Strategy aggregate navigation of real summary tables would not miss the opportunity. For example, suppose a base table exists at the Day level and a user submits a query at the Year level. If a physical table is created at the Month level, the Strategy application will access that table to fulfill the Year level query; Oracle does not inherently know the relationship between Year and Month. This can be overcome by redefining the dimensional model in Oracle via DIMENSIONS and HIERARCHIES, but it does represent additional work to set up.
With the introduction of Oracle Real-time Materialized View, it should be a good choice to store pre-aggregated data in Materialized View, and let Strategy be aware of the MV as a physical aggregate table. In this setup, Oracle will be able to maintain the availability of the Materialized View and Strategy will be able to generate SQL to fully utilize the MV with the better understanding of the logical relations.
