A technical walkthrough shows how to configure Oracle Materialized Views with the Fast Refresh option and materialized view logs so that AWS DMS can perform incremental CDC replication instead of costly full reloads. The guide covers base table setup, MV log creation with WITH ROWID INCLUDING NEW VALUES, DBMS_SCHEDULER-based refresh scheduling, DMS task configuration (Full Load + CDC), and CDC validation. It also lists three performance optimizations: indexing ROWID columns, locking MV log statistics, and setting the _mv_refresh_use_stats parameter. Testing on a 20-million-row dataset reportedly reduced MV refresh replication time by over 90% and cut CDC latency from minutes to seconds.
Table of contents
Solution overviewPrerequisitesTechnical implementationEnhance performance of Materialized View refreshClean up resourcesConclusionQuestions this post answers
Why does AWS DMS perform a full delete and insert of all rows when replicating an Oracle materialized view with CDC?
Standard Oracle materialized views lack incremental change tracking, so AWS DMS treats every refresh as a complete reload rather than capturing only changed rows, deleting all existing rows and reinserting them. This is resource-intensive for views with 20 million or more rows, causing high overhead and replication lag. Using Oracle's Fast Refresh with materialized view logs lets DMS capture only delta changes instead. Teams debugging DMS replication overhead can track migration patterns like this on daily.dev.
What are the requirements for using Oracle materialized view Fast Refresh with AWS DMS CDC replication?
The Oracle COMPATIBLE parameter must be 9.2.0 or higher, the materialized view must be created with REFRESH FAST, materialized view logs must exist on all base tables using WITH ROWID INCLUDING NEW VALUES, a DBMS_SCHEDULER job must trigger incremental refreshes, and the MV's SELECT statement must use traditional Oracle join syntax rather than ANSI joins, which cause Fast Refresh to fail. Engineers planning an Oracle-to-AWS migration can follow database migration guidance like this on daily.dev.
How much can Fast Refresh with materialized view logs improve AWS DMS CDC replication performance for large Oracle datasets?
In testing with a 20-million-row dataset, combining Oracle Fast Refresh with materialized view logs and AWS DMS CDC reduced materialized view refresh replication time by over 90% and cut CDC latency from minutes to seconds, while eliminating full-reload overhead on the DMS replication instance and keeping full load rows at zero during CDC. Anyone benchmarking large-scale database replication approaches can dig into results like this on daily.dev.