Loading…
← Back to schedule
How to Get Rid of Materialized Views Using Hashing in ELT/ETL
Tuesday March 25, 2025 10:15am - 11:00am MDT
Room 209
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.


Speakers
avatar for Konstantin Zhernevskiy

Konstantin Zhernevskiy

Senior DWH Architect, Data Intensity
Konstantin is a senior DWH architect at Data Intensity with over 20 years of experience in the Oracle technology stack. For the past 10 years, he has focused on delivering data marts, data warehouses, and analytical reporting applications. His expertise includes AI, machine learning... Read More →
Tuesday March 25, 2025 10:15am - 11:00am MDT
Room 209
  Development, Infrastructure (including database)

Sign up or log in to save this to your schedule, view media, leave feedback and see who's attending!

Tweet Share
Share Modal

Share this link via

Or copy link