Fast Refresh of Mview Taking 20 Min. Due to Hints & Full Table Scans of 20gb Table (Doc ID 733054.1)

Last updated on JUNE 30, 2017

Applies to:

Oracle Database - Enterprise Edition - Version 10.2.0.3 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

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