Materialized Views

DBMS_MVIEW.EXPLAIN_MVIEW

As we saw in the preceding topic, predicting whether or not a materialized view is fast refreshable can be complicated. The DBMS_MVIEW.EXPLAIN_MVIEW utility can simplify this task however. Full details on how the utility works are available at the preceding link. The material below will help you use the utility effectively.

MV_CAPABILITIES_TABLE

There are two ways to get the output from DBMS_MVIEW.EXPLAIN_MVIEW, via a table or via a varray. To use the table method the current schema must contain a table called MV_CAPABILITIES_TABLE. The full, documented CREATE TABLE command for MV_CAPABILITIES_TABLE can be found on UNIX systems at $ORACLE_HOME/rdbms/admin/utlxmv.sql. It is also available in Oracle's documentation at Oracle Database Data Warehousing Guide - Basic Materialized Views - Using MV_CAPABILITIES_TABLE (see Gotcha for a related bug). Here is an abridged version.

create table MV_CAPABILITIES_TABLE
(
  statement_id      varchar(30) ,
  mvowner           varchar(30) ,
  mvname            varchar(30) ,
  capability_name   varchar(30) ,
  possible          character(1) ,
  related_text      varchar(2000) ,
  related_num       number ,
  msgno             integer ,
  msgtxt            varchar(2000) ,
  seq               number
) ;
 

VARRAY Output

Using DBMS_MVIEW.EXPLAIN_MVIEW with the table output method typically involves

  1. deleting old rows from MV_CAPABILITIES_TABLE
  2. running DBMS_MVIEW.EXPLAIN_MVIEW
  3. selecting new rows from MV_CAPABILITIES_TABLE.

To save time in this tutorial we will use DBMS_MVIEW.EXPLAIN_MVIEW's varray output option instead and supplement it with a custom function called MY_MV_CAPABILITIES.

create or replace function my_mv_capabilities
(
  p_mv                       in  varchar2 ,
  p_capability_name_filter   in  varchar2 default '%' ,
  p_include_pct_capabilities in  varchar2 default 'N' ,
  p_linesize                 in  number   default 80
)
  return clob
as
  --------------------------------------------------------------------------------
  -- From http://www.sqlsnippets.com/en/topic-12884.html
  --
  -- Parameters:
  --
  --   p_mv
  --     o this value is passed to DBMS_MVIEW.EXPLAIN_MVIEW's "mv" parameter
  --     o it can contain either a query, CREATE MATERIALIZED VIEW command text,
  --       or a materialized view name
  --
  --   p_capability_name_filter
  --     o use either REFRESH, REWRITE, PCT, or the default
  --
  --   p_include_pct_capabilities
  --     Y - capabilities like REFRESH_FAST_PCT are included in the report
  --     N - capabilities like REFRESH_FAST_PCT are not included in the report
  --
  --   p_linesize
  --     o the maximum size allowed for any line in the report output
  --     o data that is longer than this value will be word wrapped
  --
  -- Typical Usage:
  --
  --   set long 5000
  --   select my_mv_capabilities( 'MV_NAME' ) as mv_report from dual ;
  --
  --   o the value 5000 is arbitraty; any value big enough to contain the
  --     report output will do
  --
  --------------------------------------------------------------------------------

  pragma autonomous_transaction ;

  v_nl constant char(1) := unistr( '\000A' ); -- new line

  v_previous_possible char(1) := 'X' ;

  v_capabilities sys.ExplainMVArrayType ;

  v_output clob ;

begin

  dbms_mview.explain_mview( mv => p_mv, msg_array => v_capabilities ) ;

  for v_capability in
  (
    select
      capability_name ,
      possible ,
      related_text ,
      msgtxt
    from
      table( v_capabilities )
    where
      capability_name like '%' || upper( p_capability_name_filter ) || '%' and
      not
        ( capability_name like '%PCT%' and
          upper(p_include_pct_capabilities) = 'N'
        )
    order by
      mvowner ,
      mvname ,
      possible desc ,
      seq
  )
  loop

    ------------------------------------------------------------
    -- print section heading
    ------------------------------------------------------------

    if v_capability.possible <> v_previous_possible then

      v_output :=
        v_output
        || v_nl
        || case v_capability.possible
           when 'T' then 'Capable of: '
           when 'Y' then 'Capable of: '
           when 'F' then 'Not Capable of: '
           when 'N' then 'Not Capable of: '
           else v_capability.possible || ':'
           end
        || v_nl
      ;

    end if;

    v_previous_possible := v_capability.possible ;

    ------------------------------------------------------------
    -- print section body
    ------------------------------------------------------------
    declare

      v_indented_line_size varchar2(3) := to_char( p_linesize - 5 );

    begin

      -- print capability name indented 2 spaces

      v_output :=
        v_output
        || v_nl
        || '  '
        || v_capability.capability_name
        || v_nl
      ;

      -- print related text indented 4 spaces and word wrapped

      if v_capability.related_text is not null then

        v_output :=
          v_output
          || regexp_replace
             ( v_capability.related_text || ' '
             , '(.{1,'
                 || v_indented_line_size || '} |.{1,'
                 || v_indented_line_size || '})'
             , '    \1' || v_nl
             )
        ;

      end if;

      -- print message text indented 4 spaces and word wrapped

      if v_capability.msgtxt is not null then

        v_output :=
          v_output
          || regexp_replace
             ( v_capability.msgtxt || ' '
             , '(.{1,'
                 || v_indented_line_size || '} |.{1,'
                 || v_indented_line_size || '})'
             , '    \1' || v_nl
             )
        ;

      end if;

    end;

  end loop;

  commit ;

  return( v_output );

end;
/

show errors
No errors.
 

This completes our preparations. Now let's see DBMS_MVIEW.EXPLAIN_VIEW in action.

DBMS_MVIEW.EXPLAIN_MVIEW With a Query

DBMS_MVIEW.EXPLAIN_MVIEW can analyze three different types of materialized view code:

  1. a defining query
  2. a CREATE MATERIALIZED VIEW command
  3. an existing materialized view.

Here is an example that explains a simple query which could appear as the defining query in a CREATE MATERIALIZED VIEW command.

set long 5000

select my_mv_capabilities( 'SELECT * FROM T', 'REFRESH' ) as mv_report from dual ;
 
MV_REPORT
--------------------------------------------------------------------------------

Capable of:

  REFRESH_COMPLETE

Not Capable of:

  REFRESH_FAST

  REFRESH_FAST_AFTER_INSERT
    SCOTT.T
    the detail table does not have a materialized view log

  REFRESH_FAST_AFTER_ONETAB_DML
    see the reason why REFRESH_FAST_AFTER_INSERT is disabled

  REFRESH_FAST_AFTER_ANY_DML
    see the reason why REFRESH_FAST_AFTER_ONETAB_DML is disabled
 

(Descriptions of each capability name are available at Table 8-7 CAPABILITY_NAME Column Details. A list of messages and related text is available at Table 8-8 MV_CAPABILITIES_TABLE Column Details.)

The EXPLAIN_MVIEW output above shows that fast refresh is not possible in this case because T has no materialized view log.

Note that DBMS_MVIEW.EXPLAIN_MVIEW can report on a materialized view's refresh, rewrite, and partition change tracking (PCT) capabilities. For now we will only examine refresh capabilities. Rewrite capabilities will be covered in Query Rewrite Restrictions and Capabilities.

DBMS_MVIEW.EXPLAIN_MVIEW With CREATE MATERIALIZED VIEW

Now let's create a materialized view log on T and then use EXPLAIN_MVIEW to explain the capabilities of an entire CREATE MATERIALIZED VIEW command.

create materialized view log on t ;

select
  my_mv_capabilities
  ( 'CREATE MATERIALIZED VIEW MV REFRESH FAST AS SELECT * FROM T'
  , 'REFRESH'
  ) as mv_report
from dual ;
 
MV_REPORT
--------------------------------------------------------------------------------

Capable of:

  REFRESH_COMPLETE

  REFRESH_FAST

  REFRESH_FAST_AFTER_INSERT

  REFRESH_FAST_AFTER_ONETAB_DML

  REFRESH_FAST_AFTER_ANY_DML
 

This time we see that a materialized view using our simple query could be fast refreshable in all cases.

DBMS_MVIEW.EXPLAIN_MVIEW With Existing Materialized View

For our last example we will explain an existing materialized view, the insert-only one we saw in the preceding topic REFRESH FAST Categories.

create materialized view log on t2
  with primary key, rowid, sequence ( t_key, amt )
  including new values
;

create materialized view mv
  refresh fast
  as
    select t_key, max( amt ) amt_max
    from t2
    group by t_key
;

select my_mv_capabilities( 'MV', 'REFRESH' ) as mv_report from dual ;
 
MV_REPORT
--------------------------------------------------------------------------------

Capable of:

  REFRESH_COMPLETE

  REFRESH_FAST

  REFRESH_FAST_AFTER_INSERT

Not Capable of:

  REFRESH_FAST_AFTER_ONETAB_DML
    mv uses the MIN or MAX aggregate functions

  REFRESH_FAST_AFTER_ONETAB_DML
    COUNT(*) is not present in the select list

  REFRESH_FAST_AFTER_ANY_DML
    see the reason why REFRESH_FAST_AFTER_ONETAB_DML is disabled
 

Here we see that fast refresh is available after inserts, but not other types of DML. Note also that the "REFRESH_FAST" capability will appear whenever at least one of the other REFRESH_FAST_% capabilities is available. It does not mean the materialized view is fast refreshable in all cases.

Gotcha

Both the $ORACLE_HOME/rdbms/admin/utlxmv.sql file and the CREATE TABLE command at Oracle Database Data Warehousing Guide - Basic Materialized Views - Using MV_CAPABILITIES_TABLE state the values in MV_CAPABILITIES_TABLE.POSSIBLE will either be "T" or "F".

CREATE TABLE MV_CAPABILITIES_TABLE
...
POSSIBLE          CHARACTER(1),  -- T = capability is possible
                                 -- F = capability is not possible
...
 

In actual use we can see the values are really "Y" and "N".

delete from mv_capabilities_table ;

execute dbms_mview.explain_mview( 'select * from t' );

commit;

column possible format a8

select distinct POSSIBLE from mv_capabilities_table ;
 
POSSIBLE
--------
Y
N
 

The values "T" and "F" are, however, used when DBMS_MVIEW.EXPLAIN_MVIEW output is saved to a varray.

Cleanup

set long 80

drop materialized view mv ;

drop materialized view log on t ;
drop materialized view log on t2 ;
 



Linking to SQL Snippets ™

To link to this page in Oracle Technology Network Forums or OraFAQ Forums cut and paste this code.

  • [url=http://www.sqlsnippets.com/en/topic-12884.html]SQL Snippets: Materialized Views - DBMS_MVIEW.EXPLAIN_MVIEW[/url]

To link to this page in HTML documents or Blogger comments cut and paste this code.

  • <a href="http://www.sqlsnippets.com/en/topic-12884.html">SQL Snippets: Materialized Views - DBMS_MVIEW.EXPLAIN_MVIEW</a>

To link to this page in other web sites use the following values.

  • Link Text : SQL Snippets: Materialized Views - DBMS_MVIEW.EXPLAIN_MVIEW
  • URL (href): http://www.sqlsnippets.com/en/topic-12884.html