Data Warehouse Row Update Optimization via Identifier Sorting

Solution Overview

Problem

Update operations in data warehouses are resource-intensive and inefficient, particularly when discrete updates are performed, leading to excessive data block accesses and increased resource consumption.

Innovation Solution

Allocating a sequence of unique identifiers to rows in a data warehouse and sorting data elements according to these identifiers for successive updates, reducing the number of data block accesses and optimizing resource usage.

Engineering Contradictions & Design Principles

VSEngineering Contradiction Analysis

1Ease of operation

If discrete update operations are performed on rows in a data warehouse, then individual row modifications can be made, but the number of data block accesses increases and resource consumption increases

Engineering Contradiction:

Improverow update capabilityVSAvoidresource consumption

Core Design Contradiction:

Ease of operationVSUse of energy by moving object

Solution Approach 1:

The patent combines multiple discrete update operations into a single bulk update operation. Instead of processing each row update separately (which causes multiple data block accesses), the system collects multiple update operations and executes them together in one operation, significantly reducing the number of data block accesses and resource consumption.

Inventive Principle:

Principle #5Merging (Combining)

Solution Approach 2:

The patent performs preliminary sorting of update operations based on row identifiers before executing the bulk update. By pre-sorting the updates in the order they appear in the data warehouse, the system ensures that consecutive updates access consecutive data blocks, minimizing I/O operations and optimizing resource usage during the update process.

Inventive Principle:

Principle #10Preliminary action

2Ease of operation

If discrete update operations are performed on rows in a data warehouse, then individual row modifications can be made, but the number of data block accesses increases

Engineering Contradiction:

Improverow update capabilityVSAvoiddata block access time

Core Design Contradiction:

Ease of operationVSLoss of time

Solution Approach 1:

The patent combines multiple discrete update operations into a single bulk update operation. Instead of processing each row update separately (which causes multiple data block accesses), the system collects multiple update operations and executes them together in one operation, significantly reducing the number of data block accesses and resource consumption.

Inventive Principle:

Principle #5Merging (Combining)

Solution Approach 2:

The patent performs preliminary sorting of update operations based on row identifiers before executing the bulk update. By pre-sorting the updates in the order they appear in the data warehouse, the system ensures that consecutive updates access consecutive data blocks, minimizing I/O operations and optimizing resource usage during the update process.

Inventive Principle:

Principle #10Preliminary action

3Reliability

If traditional update methods are used in data warehouses, then updates can be performed on existing rows, but the operations are resource intensive and expensive

Engineering Contradiction:

Improveupdate operation reliabilityVSAvoidupdate processing efficiency

Core Design Contradiction:

ReliabilityVSProductivity

Solution Approach 1:

The patent combines multiple discrete update operations into a single bulk update operation. Instead of processing each row update separately (which causes multiple data block accesses), the system collects multiple update operations and executes them together in one operation, significantly reducing the number of data block accesses and resource consumption.

Inventive Principle:

Principle #5Merging (Combining)

Solution Approach 2:

The patent changes the operational parameters of the update process by switching from discrete row-by-row updates to bulk batch updates. This parameter change fundamentally alters the efficiency characteristics by processing multiple rows simultaneously and optimizing data block access patterns through sorting, thereby improving productivity while maintaining reliability.

Inventive Principle:

Principle #35Parameter changes

Data Source

PatentUS8868484B2Efficiently updating rows in a data warehouse

Publication Date: 2014.10.21 ORACLE INT CORP

AI summary

An aspect of the present invention facilitates efficient updating of rows in a data warehouse. In one embodiment, a sequence of identifiers is allocated to rows of data elements received earlier, and then inserted along with the received data elements in a table according to the defined by the sequence of identifiers. On receiving data elements as updates to the rows having associated identifiers, the received data elements are sorted according to the order of the associated identifiers and updates of the rows are performed successively in the sorted order of identifiers. According to another aspect, the above features are performed when (target) fact/dimension tables in the data warehouse are sought to be updated based on transformations/computations performed on data in other (source) fact/dimension tables.