Fast Refresh is not Possible with Materialized Views Containing Virtual Columns (Doc ID 1207199.1)

Last updated on AUGUST 22, 2014

Applies to:

Oracle Database - Enterprise Edition - Version 10.2.0.1 and later
Information in this document applies to any platform.

Symptoms

Fast refresh is not possible with aggregate materialized views containing
virtual columns.


SQL> select *
2 FROM MV_CAPABILITIES_TABLE
3 where statement_id = 't270_t163_agg_join_vc_mv#1'
4 ORDER BY seq;

STATEMENT_ID MVOWNER MVNAME CAPABILITY_NAME P RELATED_TEXT RELATED_NUM MSGNO MSGTXT SEQ
---------------------------- -------- -------- ------------------------------ - ------------------------- ----------- ---------- ------------------------------------------------------------------------------------------ ----------
t270_t163_agg_join_vc_mv#1 PCT N 1
t270_t163_agg_join_vc_mv#1 REFRESH_COMPLETE Y 1002
t270_t163_agg_join_vc_mv#1 REFRESH_FAST N 2003
t270_t163_agg_join_vc_mv#1 REWRITE Y 3004
t270_t163_agg_join_vc_mv#1 PCT_TABLE N T227_AGG_MV 116 2068 relation is not a partitioned table 4005
t270_t163_agg_join_vc_mv#1 PCT_TABLE N T163_AGG_VC_MV 132 2068 relation is not a partitioned table 4006
t270_t163_agg_join_vc_mv#1 REFRESH_FAST_AFTER_INSERT N TEST.T163_AGG_VC_MV 2081 mv log does not have all necessary columns 5007
t270_t163_agg_join_vc_mv#1 REFRESH_FAST_AFTER_ONETAB_DML N 2146 see the reason why REFRESH_FAST_AFTER_INSERT is disabled 6008
t270_t163_agg_join_vc_mv#1 REFRESH_FAST_AFTER_ANY_DML N 2161 see the reason why REFRESH_FAST_AFTER_ONETAB_DML is disabled 7009
t270_t163_agg_join_vc_mv#1 REFRESH_FAST_PCT N 2157 PCT is not possible on any of the detail tables in the materialized view 8010
t270_t163_agg_join_vc_mv#1 REWRITE_FULL_TEXT_MATCH Y 9011

STATEMENT_ID MVOWNER MVNAME CAPABILITY_NAME P RELATED_TEXT RELATED_NUM MSGNO MSGTXT SEQ
---------------------------- -------- -------- ------------------------------ - ------------------------- ----------- ---------- ------------------------------------------------------------------------------------------ ----------
t270_t163_agg_join_vc_mv#1 REWRITE_PARTIAL_TEXT_MATCH Y 10012
t270_t163_agg_join_vc_mv#1 REWRITE_GENERAL Y 11013
t270_t163_agg_join_vc_mv#1 REWRITE_PCT N 2158 general rewrite is not possible or PCT is not possible on any of the detail tables 12014
t270_t163_agg_join_vc_mv#1 PCT_TABLE_REWRITE N T227_AGG_MV 116 2068 relation is not a partitioned table 13015
t270_t163_agg_join_vc_mv#1 PCT_TABLE_REWRITE N T163_AGG_VC_MV 132 2068 relation is not a partitioned table 13016

16 rows selected.


The message is :"mv log does not have all necessary columns "

Cause

Sign In with your My Oracle Support account

Don't have a My Oracle Support account? Click to get started

My Oracle Support provides customers with access to over a
Million Knowledge Articles and hundreds of Community platforms