Dbms_mview Package

Oracle Documentation

Note : DBMS_SNAPSHOT is a synonym for DBMS_MVIEW.

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:

• COUNT(*)
• COUNT(amount_sold)

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.

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

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.