Materialized View Non Unique Index grows after every refresh, the rebuild fails

(Doc ID 1314796.1)

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.

Symptoms

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:

CREATE MATERIALIZED VIEW "USRMGR"."MV_AVANZAMENTO_CDT"
...
BUILD IMMEDIATE
USING INDEX
REFRESH COMPLETE ON DEMAND...
AS SELECT cdt.aggiornamento, cdt.id_att, cdt.id_sis,
...
FROM infodlv_attivita att,
infodlv_cdt cdt1,
infodlv_lanci l,
infodlv_attivita_r_sistemi rs,
infodlv_e_kit kit
WHERE rs.attivita = att.ID...;

CREATE INDEX "USRMGR"."IDX_MV_AVANZ_CDT" ON "USRMGR"."MV_AVANZAMENTO_CDT" ("ID_ATT", "ID_SIS", "KIT")...
TABLESPACE "TS_INDEX";


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. "


Changes

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.

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