Materialized View Non Unique Index grows after every refresh, the rebuild fails
Last updated on JANUARY 18, 2017
Applies to:Oracle Database - Enterprise Edition - Version 10.2.0.4 and later
Information in this document applies to any platform.
During a COMPLETE refresh of a Materialized View an index on the mview grows unexpectedly from some Megabytes to over a Gigabyte. It appears as if the Index is not rebuilding as expected.
The DDL to build the mview and the Index are:
REFRESH COMPLETE ON DEMAND...
AS SELECT cdt.aggiornamento, cdt.id_att, cdt.id_sis,
FROM infodlv_attivita att,
WHERE rs.attivita = att.ID...;
CREATE INDEX "USRMGR"."IDX_MV_AVANZ_CDT" ON "USRMGR"."MV_AVANZAMENTO_CDT" ("ID_ATT", "ID_SIS", "KIT")...
When the Mview in question was refreshed using the following:
BEGIN DBMS_SNAPSHOT.REFRESH( 'MV_AVANZAMENTO_CDT','C'); END;
The Index IDX_MV_AVANZ_CDT grew larger and larger.
The expected behaviour here is (from article Note:889342.1 ):
"In the case of a COMPLETE refresh, this requires temporary sort space to REBUILD all indexes
during refresh process. This is because the COMPLETE refresh truncates or deletes the table
before inserting the new full data volume. "
It was suspected that the Index was not being rebuilt successfully from the REFRESH statement.
So a COMPLETE refresh was traced using the 10046 event. The trace file produced highlighted
the following for the recursive REBUILD statement.
PARSE ERROR #28:len=62 dep=0 uid=143 oct=47 lid=143 tim=670554323 err=6550
BEGIN immediate 'alter index IDX_MV_AVANZ_CDT rebuild'; END;
So the REBUILD was indeed failing.
Sign In with your My Oracle Support account
Don't have a My Oracle Support account? Click to get started
Million Knowledge Articles and hundreds of Community platforms