oracle - Materialized View involving UNION ALL operator in FROM with ON COMMIT -

i have 2 tables 'survey' & 'survey_processed' , both similar tables. have working query uses union operator between 2 tables & getting counts. when try translate in materialized view, error related on commit. check out mv ddl & error below.

      create materialized view vwm_survey_records_count     refresh fast on commit         select         survey_combined.survey_header_id,         count(*) count_total,         count(case when survey_combined.processed_flag = 'y' 1 else null end) count_a,         count(case when survey_combined.approved_flag null 1 else null end) count_b,         count(case when survey_combined.processed_flag = 'n' , survey_combined.approved_flag = 'y' 1 else null end) count_c,         count(case when survey_combined.approved_flag = 'n' 1 else null end) count_d          (     select survey_header_id, 'n' processed_flag, approved_flag survey     union     select survey_header_id, 'y' processed_flag, approved_flag survey_processed) survey_combined     inner join survey_header on survey_combined.survey_header_id = survey_header.id     group survey_combined.survey_header_id;  

error if run above command:
'sql error: ora-12054: cannot set on commit refresh attribute materialized view' .

but, if use 'refresh complete on demand' works. know breaking restrictions on commit attribute, can't figure out which. can let me know doing wrong on above query? also, there better approach query such make efficient & work 'refresh fast on commit', while creating mv.

note: have mv log created both tables using rowid on selected columns.
let me know if has questions.

thanks in advance.

here's ddl mv log requested 'jonearles'

      create materialized view log on survey sequence,rowid (id, survey_header_id, approved_flag, processed_flag) including new values;     create materialized view log on survey_processed sequence,rowid (id, survey_header_id, approved_flag) including new values;     create materialized view log on survey_header sequence,rowid (id) including new values; 

note: column 'processed_flag' in 'survey' table dropped later on. technically, 2 tables split based on value of 'processed_flag' column. so, 'survey' table has un-processed records (processed_flag = 'n') & 'survey_processed' has processed records (processed_flag = 'y'). after split, column irrelevant.

i think you're out of luck here. restrictions on fast refresh on materialized views union all:

the defining query must have union operator @ top level.

and replacing union all outer joins doesn't work either. aggregation , outer joins don't appear work together. code below not equivalent yours, demonstrates simplified version of query won't work:

create materialized view vwm_survey_records_count refresh complete on demand select survey_header.id survey_header_id, count(*) count_total survey_header, survey, survey_processed survey_header.id = survey.survey_header_id(+) group survey_header.id;   delete mv_capabilities_table; begin     dbms_mview.explain_mview ('vwm_survey_records_count'); end; / select possible, msgno, msgtxt mv_capabilities_table capability_name = 'refresh_fast_after_insert';  possible    msgno   msgtxt --------    -----   ------ n           2048    outer join in mv