Explain MView

A recent discussion (https://community.oracle.com/message/13240100) triggered me to check he DBMS_MVIEW.EXPLAIN_MVIEW logic.
This procedure gives information about a materialized view. Especially reasons why query rewrite or fast refreshes are not possible.

The simple version needs a table MV_CAPABILITIES_TABLE. In most systems this table is not installed. But the second version of the EXPLAIN_MVIEW logic can be used. It returns a varray of ExplainMVMessage type.

Here is a simple pipelined function that allows to call this dbms_package.

create or replace
function explainMview (p_mview in varchar2) return SYS.ExplainMVArrayType pipelined
authid current_user
as
  pragma autonomous_transaction;
  v_expl SYS.ExplainMVArrayType;
  v_msg  SYS.ExplainMVMessage;
  v_ind  binary_integer;
begin
  dbms_mview.explain_mview(mv => p_mview, msg_array => v_expl);
  commit;

  v_ind := v_expl.first;
  loop
    v_msg := v_expl(v_ind);
    pipe row (v_msg);

    exit when v_ind >= v_expl.last;
    v_ind := v_expl.next(v_ind);
  end loop;

end explainMview;

In general I install that package in some DBA account. And then give

grant execute on explainMview to someSchema;

After that any relevant users can use it to get the explaination.
here is an example:

select * from table(isdba.explainMview('MYSCHEMA.MV_CLI_PROGRAMME'));
MVOWNER MVNAME CAPABILITY_NAME POSSIBLE RELATED_TEXT RELATED_NUM MSGNO MSGTXT SEQ
MYSCHEMA MV_CLI_PROGRAMME_USER PCT F   0 0   1
MYSCHEMA MV_CLI_PROGRAMME_USER REFRESH_COMPLETE T   0 0   1002
MYSCHEMA MV_CLI_PROGRAMME_USER REFRESH_FAST T   0 0   2003
MYSCHEMA MV_CLI_PROGRAMME_USER REWRITE F   0 0   3004
MYSCHEMA MV_CLI_PROGRAMME_USER PCT_TABLE F ESM.PERSON 216 2068 relation is not a partitioned table 4005
MYSCHEMA MV_CLI_PROGRAMME_USER PCT_TABLE F ESM.PERSONTEAMPERSON 262 2068 relation is not a partitioned table 4006
MYSCHEMA MV_CLI_PROGRAMME_USER REFRESH_FAST_AFTER_INSERT T   0 0   5007
MYSCHEMA MV_CLI_PROGRAMME_USER REFRESH_FAST_AFTER_ONETAB_DML T   0 0   6008
MYSCHEMA MV_CLI_PROGRAMME_USER REFRESH_FAST_AFTER_ANY_DML T   0 0   7009
MYSCHEMA MV_CLI_PROGRAMME_USER REFRESH_FAST_PCT F   0 2197 PCT FAST REFRESH is not possible if query contains a remote table 8010
MYSCHEMA MV_CLI_PROGRAMME_USER REWRITE_FULL_TEXT_MATCH F ESM.PERSON 216 2099 mv references a remote table or view in the FROM list 9011
MYSCHEMA MV_CLI_PROGRAMME_USER REWRITE_FULL_TEXT_MATCH F ESM.PERSONTEAMPERSON 262 2099 mv references a remote table or view in the FROM list 9012
MYSCHEMA MV_CLI_PROGRAMME_USER REWRITE_FULL_TEXT_MATCH F   0 2159 query rewrite is disabled on the materialized view 9013
MYSCHEMA MV_CLI_PROGRAMME_USER REWRITE_PARTIAL_TEXT_MATCH F   0 2159 query rewrite is disabled on the materialized view 10014
MYSCHEMA MV_CLI_PROGRAMME_USER REWRITE_GENERAL F   0 2159 query rewrite is disabled on the materialized view 11015
MYSCHEMA MV_CLI_PROGRAMME_USER REWRITE_PCT F   0 2158 general rewrite is not possible or PCT is not possible on any of the detail tables 12016
MYSCHEMA MV_CLI_PROGRAMME_USER PCT_TABLE_REWRITE F ESM.PERSON 216 2068 relation is not a partitioned table 13017
MYSCHEMA MV_CLI_PROGRAMME_USER PCT_TABLE_REWRITE F ESM.PERSONTEAMPERSON 262 2068 relation is not a partitioned table 13018
Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s