Materialized View With Fast Refresh
(Doc ID 1093616.1)
Last updated on SEPTEMBER 28, 2021
Applies to:
Oracle Database Cloud Schema Service - Version N/A and laterOracle Database Backup Service - Version N/A and later
Oracle Database Cloud Service - Version N/A and later
Oracle Database - Enterprise Edition - Version 10.2.0.4 to 11.2.0.2 [Release 10.2 to 11.2]
Gen 1 Exadata Cloud at Customer (Oracle Exadata Database Cloud Machine) - Version N/A and later
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"."T1" SNA$
WHERE
"MAN_ROWID" IN
(SELECT /*+ NO_MERGE HASH_SJ */ * FROM
(SELECT CHARTOROWID("MAS$"."M_ROW$$") RID$
FROM
"TEST"."MLOG$_TEST"@<dblink_name> "MAS$" WHERE "MAS$".SNAPTIME$$ > :1 )MAS$)
Rows Row Source Operation
------- ---------------------------------------------------
0 DELETE T1 (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 T1003 (cr=1 pr=0 pw=0 time=39 us)(object id 185118)
1 REMOTE MLOG$_TEST (cr=0 pr=0 pw=0 time=4250 us)
For each table the mview is based on.
Changes
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 |