Materialized View With Fast Refresh (Doc ID 1093616.1)

Last updated on MARCH 30, 2016

Applies to:

Oracle Server - Enterprise Edition - Version: 10.2.0.4 to 11.2.0.2 - Release: 10.2 to 11.2
Information in this document applies to any platform.

Symptoms

During the fast refresh of a join mview where the master tables are on a remote database, the recursive delete command on the mview container table performs a full table scan on each master table (or via index if there is any) even when just few rows are impacted.

That full scan on a big table (even via index) makes refresh slow.

DELETE FROM "TEST"."REFMEETICT" SNA$
WHERE
"MAN_ROWID" IN

    (SELECT /*+ NO_MERGE HASH_SJ */ * FROM

       (SELECT CHARTOROWID("MAS$"."M_ROW$$") RID$

FROM 
"TEST"."MLOG$_ANNONCE"@dblink "MAS$" WHERE "MAS$".SNAPTIME$$ > :1 )MAS$)


Rows Row Source Operation
------- --------------------------------------------------- 
0 DELETE REFMEETICT (cr=1 pr=0 pw=0 time=6097 us) 
1 HASH JOIN SEMI (cr=1 pr=0 pw=0 time=5929 us) 
3 INDEX FULL SCAN REFMEETICT003 (cr=1 pr=0 pw=0 time=39 us)(object id 185118) 
1 REMOTE MLOG$_ANNONCE (cr=0 pr=0 pw=0 time=4250 us)

For each table the mview is based on.

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