Fast Refresh of Mview Taking 20 Min. Due to Hints & Full Table Scans of 20gb Table
(Doc ID 733054.1)
Last updated on APRIL 07, 2020
Applies to:
Oracle Database - Enterprise Edition - Version 10.2.0.3 and laterOracle Database Cloud Schema Service - Version N/A and later
Oracle Database Exadata Express Cloud Service - Version N/A and later
Oracle Database Exadata Cloud Machine - Version N/A and later
Oracle Cloud Infrastructure - Database Service - Version N/A and later
Information in this document applies to any platform.
Symptoms
Fast refresh of a local materialized view shows a poor plan. The performance is slow due to full table scan caused by hash_sj hint. The statistics are LOCKED on the materialized view log tables (with zero rows) and _mv_refresh_use_stats is set to false.
83 TABLE ACCESS BY GLOBAL INDEX ROWID
167 NESTED LOOPS
83 NESTED LOOPS
83 VIEW
83 HASH JOIN RIGHT SEMI
83 TABLE ACCESS BY INDEX ROWID
83 INDEX RANGE SCAN
216848699 TABLE ACCESS FULL
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 |