Part 1 – Implementation: Creating Materialized View with FAST REFRESH
To enable FAST REFRESH in a materialized view, Oracle requires the existence of a Materialized View Log (MV log) on the base table. This log records the row-level changes (INSERT/UPDATE/DELETE) that occur after the materialized view is created. First, create the MV log using the CREATE MATERIALIZED VIEW LOG command on the base table, including primary key or rowid for tracking. Then, define the materialized view with REFRESH FAST option and link it to the base table. You can also schedule the refresh using START WITH and NEXT clauses or run it manually. The materialized view captures only the delta changes using the MV log and avoids full-table scans. Fast refresh reduces system overhead and improves performance. It’s most suitable for scenarios needing near real-time data synchronization. Oracle verifies the compatibility of the view definition with fast refresh rules before creation. If conditions aren’t met, fast refresh won’t be possible, and full refresh will be used instead.
Part 2 – Runtime Behavior: How Fast Refresh Works Using MV Log
When a materialized view is created with FAST REFRESH, Oracle relies on a materialized view log (MV log) to track changes (INSERT, UPDATE, DELETE) on the base table. This log captures only incremental modifications, enabling efficient delta refreshes without re-querying the entire source table. The MV log is a physical table (MLOG$_) containing metadata like DMLTYPE$$, SNAPTIME$$, and ROWID. When a DML operation occurs on the base table, it’s recorded in the log. Upon triggering a fast refresh (manually or via a scheduled job), Oracle reads the log to update only the changed rows in the materialized view. Once the refresh completes, the log entries are purged automatically, ensuring it’s ready to capture the next set of changes. This keeps the MV log light and avoids stale data. The log won’t grow endlessly and is reused across refresh cycles. This mechanism significantly boosts performance in data warehousing and reporting systems.
