UTOUG Training Days 2025

Materialized views are a robust Oracle Database feature, but they have some drawbacks. When we refresh the data and an incremental refresh (force refresh) doesn’t work, we need to fully reload the materialized view. While it’s refreshing, the data is inaccessible to business users. If it fails, we must rerun it, leaving the data inaccessible. Sometimes, the process simply takes longer than expected.

What if we need to refresh the data several times a day? What if the materialized view sources data from OLTP tables, and we can’t create additional load on the OLTP system?

Two main aspects must be considered: the source of the materialized view and the data refresh method.
We assume a staging area exists where all necessary tables are replicated, providing near real-time data.
We plan to demonstrate how a materialized view can be refactored into a combination of a view, table, and procedure (or mapping). By using a hash function, we can update data incrementally instead of performing a full load. This approach makes the process faster and ensures business users have continuous access to the data. It also enables more frequent refreshes, even approaching real-time updates.

Additionally, if this materialized view serves as a source for third-party systems, it can include attributes for further change data capture (CDC).

This approach reduces data outage time, protects against incidents, and enables close-to-real-time data for end users.