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 SALES and PRODUCTS tables in the SH schema. Before you create the real-time materialized view ensure that the required prerequisites are met.

1. Create materialized view logs on the base tables SALES and PRODUCTS.

   The following command creates a materialized view log on the SALES table:

   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 PRODUCTS table.

   CREATE MATERIALIZED VIEW LOG ON products 
   WITH ROWID
   (prod_id, prod_name, prod_category, prod_subcategory)
   INCLUDING NEW VALUES;
   

2. Create a real-time materialized view by including the ON QUERY COMPUTATION clause in the CREATE MATERIALIZED VIEW statement. The fast refresh method is used for this real-time materialized view and the ENABLE QUERY REWRITE clause 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;
   

After the 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;

If 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 SALES and PRODUCTS tables with the data in the real-time materialized view SUM_SALES_RTMV.

Example 6-8 Converting a Materialized View into a Real-time Materialized View

The materialized view SALES_RTMV is based on the SALES, TIMES, and 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.

1. Modify the materialized view definition and include the ON QUERY COMPUTATION clause to change it into a real-time materialized view.

   ALTER MATERIALIZED VIEW sales_rtmv ENABLE ON QUERY COMPUTATION;

2. Query the DBA_MVIEWS view to determine if on-query computation is enabled for SALES_RTMV.

   SELECT mview_name, on_query_computation
   FROM dba_mviews
   WHERE mview_name = 'SALES_RTMV';

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 my_rtmv:

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.

1. Create a materialized view log on the SALES table, which is the base table for the real-time materialized view being created.

2. Create a real-time materialized view mav_sum_sales with 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;

3. 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_sales real-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 EXPLAIN PLAN.

   The internally rewritten query that uses mav_sum_sales is analogous to the following statement:

   SELECT prod_id, sum_qty, sum_amt
   FROM  mav_sum_sales
   WHERE prod_id < 1000;

4. Verify that the real-time materialized view was used to provide the query result. Use the EXPLAIN PLAN statement 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.

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.

1. Create a materialized view log on the base table sales_new.

   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;

2. Create a real-time materialized view called my_rtmv with sales_new as the base table.

   The ON QUERY COMPUTATION clause 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;

3. 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;

4. 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.

5. Include the FRESH_MV hint 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_MV hint triggers on-query computation for the real-time materialized view and recomputed the fresh data.

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