Oracle 物化视图快速刷新ORA-12054: 无法设置提交刷新属性

团队，

是否无法从物化视图中的特定分区刷新数据子集？请多多指教。

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

当您在设置ON COMMIT属性时出错，请解释查询以查看问题所在:

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                                                                            
REFRESH_FAST                     N                                                                            
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  

所以，错误与分区无关!

但是... 查询也不包含子查询。

可悲的是，问题在于加入语法。您需要使用Oracle风格 (外部) 联接来获得快速刷新。

将查询更改为这些联接，然后错误更改:

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                                                                                                      
REFRESH_FAST                     N                                                                                                      
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 

仔细查看您的查询，您会发现它还有另一个问题。