My Oracle Support Banner

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

Last updated on DECEMBER 20, 2019

Applies to:

Oracle Database - Enterprise Edition - Version 10.2.0.1 and later
Oracle Database Cloud Schema Service - Version N/A and later
Oracle Database Exadata Express Cloud Service - Version N/A and later
Oracle Database Exadata Cloud Machine - Version N/A and later
Oracle Database Cloud Exadata Service - Version N/A 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_TEST_TABLE
3 where statement_id = 'tXXX_tYYY_agg_join_XX_mv#X'
4 ORDER BY seq;

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

STATEMENT_ID MVOWNER MVNAME CAPABILITY_NAME P RELATED_TEXT RELATED_NUM MSGNO MSGTXT SEQ
---------------------------- -------- -------- ------------------------------ - ------------------------- ----------- ---------- ------------------------------------------------------------------------------------------ ----------
tXXX_tYYY_agg_join_XX_mv#X REWRITE_PARTIAL_TEXT_MATCH Y 10012
tXXX_tYYY_agg_join_XX_mv#X REWRITE_GENERAL Y 11013
tXXX_tYYY_agg_join_XX_mv#X REWRITE_PCT N 2158 general rewrite is not possible or PCT is not possible on any of the detail tables 12014
tXXX_tYYY_agg_join_XX_mv#X PCT_TABLE_REWRITE N TXXY_AGG_MV 116 2068 relation is not a partitioned table 13015
tXXX_tYYY_agg_join_XX_mv#X PCT_TABLE_REWRITE N TXXX_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 "

Changes

 

Cause

To view full details, sign in with your My Oracle Support account.

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


In this Document
Symptoms
Changes
Cause
Solution
References


My Oracle Support provides customers with access to over a million knowledge articles and a vibrant support community of peers and Oracle experts.