Materialized View Non Unique Index grows after every refresh, the rebuild fails
(Doc ID 1314796.1)
Last updated on FEBRUARY 01, 2022
Applies to:
Oracle Database - Enterprise Edition - Version 10.2.0.4 and laterInformation in this document applies to any platform.
Symptoms
NOTE: In the images and/or the document content below, the user information and data used represents fictitious data from the Oracle sample schema(s) or Public Documentation delivered with an Oracle database product. Any similarity to actual persons, living or dead, is purely coincidental and not intended in any manner.
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:
When the Mview in question was refreshed using the following:
BEGIN DBMS_SNAPSHOT.REFRESH( '<MVIEW_NAME>','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
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 |