Team,
Is it not possible to refresh a subset a data from a specific partition in Materialized view? Kindly advice.
demo@PDB1> create table t1
2 partition by list( a_active_flag )
3 ( partition PY values('Y'),
4 partition PN values('N') )
5 as
6 select a.*, cast('Y' as varchar2(1)) as a_active_flag
7 from all_objects a;
Table created.
demo@PDB1> create table t2
2 partition by list( a_active_flag )
3 ( partition PY values('Y'),
4 partition PN values('N') )
5 as
6 select a.*, cast('Y' as varchar2(1)) as a_active_flag
7 from all_objects a;
Table created.
demo@PDB1> create materialized view log on t1
2 with rowid,sequence
3 including new values;
Materialized view log created.
demo@PDB1>
demo@PDB1> create materialized view log on t2
2 with rowid,sequence
3 including new values;
Materialized view log created.
demo@PDB1> create materialized view t1_t2_mv
2 build immediate
3 refresh fast on commit
4 enable query rewrite
5 as
6 select t1.owner, t1.object_type, t1.object_name,
7 t2.created,
8 t1.rowid as t1_rowid,
9 t1.rowid as t2_rowid,
10 t1.a_active_flag as t1_flag,
11 t2.a_active_flag as t2_flag
12 from t1
13 left outer join t2
14 on ( t1.object_id = t2.object_id
15 and t2.a_active_flag ='Y' )
16 where t1.a_active_flag ='Y'
17 and t1.owner ='SYS';
where t1.a_active_flag ='Y'
*
ERROR at line 16:
ORA-12054: cannot set the ON COMMIT refresh attribute for the materialized view
When you get errors setting the ON COMMIT property, explain the query to see what the issue is:
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
) ;
begin
dbms_mview.explain_mview(q'[
select t1.owner, t1.object_type, t1.object_name,
t2.created,
t1.rowid as t1_rowid,
t1.rowid as t2_rowid,
t1.a_active_flag as t1_flag,
t2.a_active_flag as t2_flag
from t1
left outer join t2
on t1.object_id = t2.object_id
and t2.a_active_flag ='Y'
where t1.a_active_flag ='Y'
and t1.owner ='SYS']');
end;
/
select capability_name, possible, msgtxt
from mv_capabilities_table
where capability_name like 'REFRESH%';
CAPABILITY_NAME POSSIBLE MSGTXT
REFRESH_COMPLETE Y <null>
REFRESH_FAST N <null>
REFRESH_FAST_AFTER_INSERT N inline view or subquery in FROM list not supported for this type MV
REFRESH_FAST_AFTER_INSERT N inline view or subquery in FROM list not supported for this type MV
REFRESH_FAST_AFTER_INSERT N view or subquery in from list
REFRESH_FAST_AFTER_ONETAB_DML N see the reason why REFRESH_FAST_AFTER_INSERT is disabled
REFRESH_FAST_AFTER_ANY_DML N see the reason why REFRESH_FAST_AFTER_ONETAB_DML is disabled
REFRESH_FAST_PCT N PCT FAST REFRESH is not possible if query contains an inline view So, the error is nothing to do with partitioning!
But... the query doesn't contain a subquery either.
Sadly the issue is down to join syntax. You need to use Oracle-style (outer) joins to get fast refresh.
Change the query to these joins and the error changes:
truncate table mv_capabilities_table;
begin
dbms_mview.explain_mview(q'[
select t1.owner, t1.object_type, t1.object_name,
t2.created,
t1.rowid as t1_rowid,
t1.rowid as t2_rowid,
t1.a_active_flag as t1_flag,
t2.a_active_flag as t2_flag
from t1, t2
where t1.a_active_flag ='Y'
and t1.owner ='SYS'
and t1.object_id = t2.object_id (+)
and t2.a_active_flag (+) ='Y' ]');
end;
/
select capability_name, possible, msgtxt
from mv_capabilities_table
where capability_name like 'REFRESH%';
CAPABILITY_NAME POSSIBLE MSGTXT
REFRESH_COMPLETE Y <null>
REFRESH_FAST N <null>
REFRESH_FAST_AFTER_INSERT N the SELECT list does not have the rowids of all the detail tables
REFRESH_FAST_AFTER_ONETAB_DML N see the reason why REFRESH_FAST_AFTER_INSERT is disabled
REFRESH_FAST_AFTER_ANY_DML N see the reason why REFRESH_FAST_AFTER_ONETAB_DML is disabled
REFRESH_FAST_PCT N PCT FAST REFRESH is not possible if query contains LEFT OUTER, RIGHT OUTER or FULL OUTER JOIN Look closely at your query and you'll see there's another issue with it.