Slow On Commit Refresh Of Materialized View With Non-Unique Outer Joins (Doc ID 578720.1)

Last updated on MAY 17, 2017

Applies to:

Oracle Database - Enterprise Edition - Version 10.2.0.1 to 10.2.0.4 [Release 10.2]
Information in this document applies to any platform.


Symptoms

-- Problem Statement:
This is an ON COMMIT Refresh Materialized View with non-unique key outer joins.
The COMMIT statements are taking long after inserting a single record into the master table.

The materialized view is ON COMMIT FAST refreshable.

The fast refresh seems slow due to the following INSERT statement (that actually inserts the records in to the mview's container table):

INSERT INTO TRAX_OWNER.DUMMY_CUST_MV_1 SELECT /*+ NO_MERGE(JV$) */ /*+ */
MAS$5.CUST_SYS_ID, MAS$4.STATE_CD, MAS$4.LIC_NBR, MAS$4.LIC_EFFECT_DT, MAS$4.LIC_RENEWAL_DT, MAS$4.LIC_STAT_CD, MAS$3.COUNTY_CD, MAS$1.STATE_NM, MAS$2.COUNTY_NM, JV$.APPNT_RENEWAL_DT, JV$.APPNT_EFFECT_DT, JV$.APPNT_TERMN_DT, JV$.UW_SYS_ID, MAS$5.ROWID, MAS$4.ROWID, MAS$3.ROWID, MAS$2.ROWID, JV$.RID$, MAS$1.ROWID FROM ( SELECT MAS$.ROWID RID$ , MAS$.*
FROM TRAX_OWNER.TR_APPOINTMENT MAS$ WHERE ROWID IN (SELECT /*+ HASH_SJ */ CHARTOROWID(MAS$.M_ROW$$) RID$ FROM TRAX_OWNER.MLOG$_TR_APPOINTMENT MAS$))
JV$, TR_STATE_CD MAS$1, TR_COUNTY MAS$2, TR_LICENSE_COUNTY MAS$3,
TR_LICENSE MAS$4, TR_CUSTOMER MAS$5 WHERE (MAS$5.CUST_SYS_ID= MAS$4.PRTY_SYS_ID(+) AND
MAS$4.SOURCE_TABLE_CD(+)='TR_CUSTOMER_LOCATION' AND
MAS$4.PRTY_SYS_ID=MAS$3.PRTY_SYS_ID(+) AND
MAS$3.SOURCE_TABLE_CD(+)='TR_CUSTOMER_LOCATION' AND MAS$4.STATE_CD=
MAS$3.STATE_CD(+) AND MAS$2.STATE_CD(+)=MAS$3.STATE_CD AND
MAS$2.COUNTY_CD(+)=MAS$3.COUNTY_CD AND MAS$1.STATE_CD(+)=
MAS$3.STATE_CD AND MAS$4.PRTY_SYS_ID=JV$.PRTY_SYS_ID(+) AND
MAS$4.SOURCE_TABLE_CD=JV$.SOURCE_TABLE_CD(+) AND MAS$4.STATE_CD=
JV$.STATE_CD(+)) AND NOT EXISTS ( SELECT 1 FROM
TRAX_OWNER.CUST_MV_1 SNA2$ WHERE (SNA2$.RID1 = MAS$5.ROWID) AND
SNA2$.RID2 = MAS$4.ROWID OR MAS$4.ROWID IS NULL ) AND
SNA2$.RID3 = MAS$3.ROWID OR MAS$3.ROWID IS NULL ) AND
SNA2$.RID4 = MAS$2.ROWID OR MAS$2.ROWID IS NULL ) AND
SNA2$.RID6 = MAS$1.ROWID OR MAS$1.ROWID IS NULL ) AND JV$.RID$
IS NULL) AND NOT EXISTS ( SELECT 1 FROM TR_APPOINTMENT MAS_INNER$,
TR_LICENSE MAS_OUTER$ WHERE MAS$4.ROWID =
MAS_OUTER$.ROWID AND JV$.RID$ IS NULL AND MAS_OUTER$.PRTY_SYS_ID=
MAS_INNER$.PRTY_SYS_ID AND MAS_OUTER$.SOURCE_TABLE_CD=
MAS_INNER$.SOURCE_TABLE_CD AND MAS_OUTER$.STATE_CD= MAS_INNER$.STATE_CD);

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