Materialized View Incremental Refresh via Partition Change Tracking

Resolve Bottlenecks,
Find Innovative SolutionsGenerate Solutions

Solution Overview

Problem

Existing incremental refresh methods for materialized views in data warehouses are inefficient, particularly when both partitioned and non-partitioned base tables are updated, as they rely heavily on materialized view logs, leading to high processing overheads and requiring complete refreshes during partition maintenance operations.

Innovation Solution

A mixed incremental refresh approach that combines log-based and partition change tracking methods to update both partition change track tables and non-partition track tables, eliminating the dependency on materialized view logs for partitioned base tables, allowing for incremental refresh without the need for log recording and maintenance.

Engineering Contradictions & Design Principles

VSEngineering Contradiction Analysis

1Productivity

If row-based incremental refresh is used for materialized views, then data synchronization speed is improved, but the system cannot handle partition maintenance operations and requires complete refresh

Engineering Contradiction:

Improvedata synchronization speedVSAvoidability to handle partition maintenance operations

Core Design Contradiction:

ProductivityVSAdaptability or versatility

Solution Approach 1:

The patent segments the materialized view refresh process into two independent components: (1) row-based incremental refresh for conventional DML operations using materialized view logs, and (2) partition-based incremental refresh for partition maintenance operations using partition change tracking. This segmentation allows each component to handle specific operation types efficiently without requiring complete refresh.

Inventive Principle:

Principle #1Segmentation

Solution Approach 2:

The patent creates a universal incremental refresh mechanism that can handle multiple types of base table changes through a single framework. The system universally supports both conventional DML operations (INSERT, UPDATE, DELETE) and partition maintenance operations (EXCHANGE PARTITION, TRUNCATE, etc.) by combining log-based and partition-based tracking methods.

Inventive Principle:

Principle #6Universality (Multi-functionality)

2Reliability

If materialized view logs are created for all base tables, then complete incremental refresh capability is achieved, but storage and processing overhead increase significantly

Engineering Contradiction:

Improveincremental refresh capabilityVSAvoidstorage overhead

Core Design Contradiction:

ReliabilityVSQuantity of substance

Solution Approach 1:

The patent applies local quality by making materialized view logs optional rather than mandatory for all base tables. The system creates logs only for non-partitioned base tables where they are needed, while partitioned base tables use partition change tracking instead. This localized approach reduces overall storage overhead while maintaining incremental refresh capability where required.

Inventive Principle:

Principle #3Local quality

Solution Approach 2:

The patent introduces partition change tracking as an intermediary mechanism that replaces the need for materialized view logs for partitioned base tables. This intermediary uses partition metadata and system change numbers to track changes without requiring full row-level logging, thereby reducing storage requirements while maintaining the ability to perform incremental refresh.

Inventive Principle:

Principle #24Intermediary (Mediator)

3Reliability

If complete refresh is performed for partition maintenance operations, then data freshness is ensured, but processing time and system resources are significantly consumed

Engineering Contradiction:

Improvedata freshnessVSAvoidrefresh processing time

Core Design Contradiction:

ReliabilityVSLoss of time

Solution Approach 1:

The patent performs preliminary action by pre-tracking partition changes through partition change tracking mechanisms that monitor system change numbers and partition metadata. When partition maintenance operations occur, the system has already captured the necessary change information, enabling immediate incremental refresh without requiring time-consuming complete recalculation of the materialized view.

Inventive Principle:

Principle #10Preliminary action

Solution Approach 2:

The patent applies partial action by refreshing only the affected portions of the materialized view corresponding to changed partitions, rather than performing a complete refresh of the entire materialized view. This partial refresh approach significantly reduces processing time and resource consumption while ensuring data freshness for the affected data portions.

Inventive Principle:

Principle #16Partial or excessive action

Data Source

PatentUS7895186B2Method and mechanism of materialized view mix incremental refresh

Publication Date: 2011.02.22 ORACLE INT CORP

AI summary

A method and system for updating both partition change track tables and non-partition track tables in a materialized view. A log-based incremental refresh is performed on the materialized view and a partition change tracking based refresh is performed on the materialized view. The dependency of the materialized view log for the partition change track base table is removed.