Note : DBMS_SNAPSHOT is a synonym for DBMS_MVIEW.
Table of Contents
1 - Details of fast refresh capabilities
You could not tell about the details of its fast refresh capabilities. But using the new dbms_mview.explain_mview package introduced with Oracle9i gives you more insight into the capabilities of the potential materialized view, so that you can address all issues before its creation.
This explain_mview procedure also works with existing materialized views.
truncate TABLE mv_capabilities_table; exec DBMS_MVIEW.explain_mview( - 'SELECT c.cust_id, SUM(amount_sold) AS dollar_sales - FROM sales s, customers c - WHERE s.cust_id= c.cust_id - GROUP BY c.cust_id'); SET serveroutput ON BEGIN FOR crec IN ( SELECT capability_name, possible, related_text, msgtxt FROM mv_capabilities_table ORDER BY 1) LOOP DBMS_OUTPUT.put_line(crec.capability_name ||': '||crec.possible); DBMS_OUTPUT.put_line(crec.related_text||': '||crec.msgtxt); END LOOP; END; /
Rather than playing trial-and-error with the system, it is recommended that you always analyze potential materialized views before their creation, using the dbms_mview.explain_mview package shown above.
Besides the missing materialized view logs on customers and sales, the system also detects that you need to add additional aggregation functions to the materialized view to fully enable fast refresh capabilities for any kind of DML operation.
The aggregation functions that the system recommends are:
The output of the dbms_mview.explain_mview package is shown here. You can see that it not only covers the refresh capabilities of a materialized view, but also the rewrite and Partition-Change-Tracking (PCT) capabilities of the materialized view. These capabilities are discussed later.
SQL> @explain_mv1.SQL TABLE truncated. PL/SQL PROCEDURE successfully completed. PCT: N : PCT_TABLE: N CUSTOMERS: relation IS NOT a partitioned TABLE PCT_TABLE: N SALES: no PARTITION key OR PMARKER OR join dependent expression IN SELECT list PCT_TABLE_REWRITE: N CUSTOMERS: relation IS NOT a partitioned TABLE PCT_TABLE_REWRITE: N SALES: no PARTITION key OR PMARKER IN SELECT list REFRESH_COMPLETE: Y : REFRESH_FAST: N : REFRESH_FAST_AFTER_ANY_DML: N : see the reason why REFRESH_FAST_AFTER_ONETAB_DML IS disabled REFRESH_FAST_AFTER_INSERT: N SH.SALES: the detail TABLE does NOT have a materialized VIEW LOG REFRESH_FAST_AFTER_INSERT: N SH.CUSTOMERS: the detail TABLE does NOT have a materialized VIEW LOG REFRESH_FAST_AFTER_ONETAB_DML: N DOLLAR_SALES: SUM(expr) without COUNT(expr) REFRESH_FAST_AFTER_ONETAB_DML: N : see the reason why REFRESH_FAST_AFTER_INSERT IS disabled REFRESH_FAST_AFTER_ONETAB_DML: N : COUNT(*) IS NOT present IN the SELECT list REFRESH_FAST_AFTER_ONETAB_DML: N : SUM(expr) without COUNT(expr) REFRESH_FAST_PCT: N : PCT IS NOT possible ON ANY OF the detail tables IN the materialized VIEW REWRITE: Y : REWRITE_FULL_TEXT_MATCH: Y : REWRITE_GENERAL: Y : REWRITE_PARTIAL_TEXT_MATCH: Y : REWRITE_PCT: N : general rewrite IS NOT possible OR PCT IS NOT possible ON ANY OF the detail tables PL/SQL PROCEDURE successfully completed.
2 - Analyzing the Rewrite Process
Another enhancement in Oracle9i was the dbms_mview.explain_rewrite procedure, which gives you detailed information about Oracle's investigation of finding a possible candidate for query rewrite in the system. To analyze the previously executed query and get more insight into the rewrite process, perform the following step:
TRUNCATE TABLE rewrite_table; DECLARE querytxt VARCHAR2(1500) := 'select c.cust_last_name, c.cust_credit_limit,' || ' SUM(amount_sold) AS dollar_sales ' || 'FROM sales s, customers c ' || 'WHERE s.cust_id= c.cust_id ' || 'GROUP BY c.cust_last_name, c.cust_credit_limit'; BEGIN DBMS_MVIEW.Explain_Rewrite(querytxt, NULL, 'ID1'); END; / SELECT message FROM rewrite_table ORDER BY sequence DESC;
If error ora-30381.
MESSAGE ------------------------------------------------------------------------------------------------------------------- QSM-01053: NORELY referential integrity constraint on table, CUSTOMERS, in TRUSTED/STALE TOLERATED integrity mode QSM-01102: materialized view, CUST_SALES_MV, requires join back to table, CUSTOMERS, on column, CUST_LAST_NAME QSM-01102: materialized view, CUST_SALES_MV, requires join back to table, CUSTOMERS, on column, CUST_CREDIT_LIMIT QSM-01101: roll(s) up took place on mv, CUST_SALES_MV QSM-01102: materialized view, CUST_SALES_AGGR, requires join back to table, CUSTOMERS, on column, CUST_CREDIT_LIMIT QSM-01101: roll(s) up took place on mv, CUST_SALES_AGRR QSM-01033: query rewritten with materialized view, CUST_SALES_MV QSM-01151: query was rewritten Elapsed: 00:00:00.06
3 - Estimating the Size of Materialized Views
Besides the improvement in creation time, you will also use less space in the database for storing the materialized views.
You can query the data dictionary to get information about the size of the materialized views. Unfortunately, this can be done only when a materialized view is already created. Ideally, you want this informatio before the creation of a materialized view, especially in very large environments. With the dbms_olap.estimate_summary_size package, you can get this information without the necessity to create the materialized view itself.
To use the Oracle database to estimate the size of the two materialized views already created and compare it with their real size, perform the following steps:
This will give you a size estimate for the materialized view containing all data.
SET serveroutput ON; DECLARE no_of_rows NUMBER; mv_size NUMBER; BEGIN no_of_rows :=555; mv_size :=5555; DBMS_OLAP.estimate_summary_size ('MV 1', 'SELECT c.cust_id, sum(s.amount_sold) AS dollars, p.prod_id, sum(s.quantity_sold) as quantity FROM SH.sales s , SH.customers c, SH.products p WHERE c.cust_id = s.cust_id AND s.prod_id = p.prod_id GROUP BY c.cust_id, p.prod_id' , no_of_rows, mv_size ); DBMS_OUTPUT.put_line ( ''); DBMS_OUTPUT.put_line ( 'Complete MV'); DBMS_OUTPUT.put_line ( 'No of Rows: ' || no_of_rows ); DBMS_OUTPUT.put_line ( 'Size of Materialized view (MB): ' || ROUND(mv_size/(1024*1024),2) ); DBMS_OUTPUT.put_line ( ''); END; /
Now look in the data dictionary to get the actual sizes of the two new materialized views.
COLUMN "MV name" format a20 SELECT SUBSTR(segment_name,1,30) "MV name", bytes/1024*1024 MB FROM user_segments WHERE segment_name IN ('SOME_CUST_SALES_MV','ALL_CUST_SALES_MV') ORDER BY segment_name ;
The materialized view containing only the subset of data is about 9 times smaller than the view containing all data. Especially in very large environments, this provides a tremendous benefit and simplifies the use of materialized views for “special analysis purposes,” touching only parts of the information in your data warehouse.