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
- deleting old rows from MV_CAPABILITIES_TABLE
- running DBMS_MVIEW.EXPLAIN_MVIEW
- 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:
- a defining query
- a CREATE MATERIALIZED VIEW command
- 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 ;