Хочу упомянуть одну полезную функцию, которую я нашёл на
SQL Snippets. Она автоматизирует весь тот процесс, который обычно выполняется при экспериментах с материализованными представлениями: вызов DBMS_MVIEW.EXPLAIN_MVIEW, вывод информации на экран, очистка MV_CAPABILITIES_TABLE. Теперь достаточно просто вызвать эту функцию в запросе, указав текст или имя материализованного представления:
SQL> set long 5000
SQL> select my_mv_capabilities(q'[select mgr, count(*) from emp group by mgr]', 'REFRESH') from dual;
MY_MV_CAPABILITIES(Q'[SELECTMGR,COUNT(*)FROMEMPGROUPBYMGR]','REFRESH')
--------------------------------------------------------------------------------
Capable of:
REFRESH_COMPLETE
Not Capable of:
REFRESH_FAST
REFRESH_FAST_AFTER_INSERT
HES.EMP
MY_MV_CAPABILITIES(Q'[SELECTMGR,COUNT(*)FROMEMPGROUPBYMGR]','REFRESH')
--------------------------------------------------------------------------------
таблица подробных данных не содержит журнал материализованного
представления
REFRESH_FAST_AFTER_ONETAB_DML
см. причину отключения REFRESH_FAST_AFTER_INSERT
REFRESH_FAST_AFTER_ANY_DML
см. причину отключения REFRESH_FAST_AFTER_ONETAB_DML
Текст самой функции ниже:
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;
/
Большое спасибо автору этой функции! Она упрощает жизнь.
Читать далее