Multi-pass Parallel Merge for Partitioned Database Pages

Solution Overview

Problem

Database management systems, such as SAP Sybase IQ, face inefficiencies in query performance due to non-contiguous row IDs in hash or hash-range partitioned tables, leading to degraded performance as the column data projection algorithm must constantly jump between different pages.

Innovation Solution

A post-load merge algorithm is implemented to merge intermediate pages into final pages with fewer row ID gaps, using a load engine configured in two phases: load and merge, where the fill factor is calculated to group intermediate pages into merge levels and trigger merging based on metadata stored in persistent data structures, ensuring efficient data projection and reduced latency.

Engineering Contradictions & Design Principles

VSEngineering Contradiction Analysis

1Productivity

If hash or hash-range partitioned tables are used for parallel bulk load, then load speed is improved, but row ID contiguity deteriorates causing query inefficiencies

Engineering Contradiction:

Improveload speedVSAvoidrow ID contiguity

Core Design Contradiction:

ProductivityVSManufacturing precision

Solution Approach 1:

The system performs a preliminary merge operation after bulk load to reorganize intermediate pages into final pages with contiguous row IDs. This preliminary action resolves the row ID fragmentation issue before query operations begin, allowing the system to maintain both high load speed and good row ID contiguity.

Inventive Principle:

Principle #10Preliminary action

Solution Approach 2:

The merge operation is divided into multiple passes working on different groups of intermediate pages in parallel. Each pass processes a subset of pages independently, allowing parallel execution while progressively eliminating row ID gaps across the entire table.

Inventive Principle:

Principle #1Segmentation

2Productivity

If intermediate pages with non-contiguous row IDs are used, then parallel bulk load efficiency is improved, but query performance deteriorates due to constant page jumping

Engineering Contradiction:

Improvebulk load efficiencyVSAvoidquery performance

Core Design Contradiction:

ProductivityVSSpeed

Solution Approach 1:

The system performs a preliminary merge operation after bulk load to reorganize intermediate pages into final pages with contiguous row IDs. This preliminary action resolves the row ID fragmentation issue before query operations begin, allowing the system to maintain both high load speed and good row ID contiguity.

Inventive Principle:

Principle #10Preliminary action

Solution Approach 2:

The merge operation uses periodic passes through the intermediate pages, with each pass processing specific groups of pages. This periodic approach systematically eliminates row ID gaps across all pages over multiple cycles, transforming intermediate pages into optimized final pages.

Inventive Principle:

Principle #19Periodic action

3Manufacturing precision

If multiple passes over data are performed to merge intermediate pages, then row ID contiguity is improved, but processing time increases

Engineering Contradiction:

Improverow ID contiguityVSAvoidprocessing time

Core Design Contradiction:

Manufacturing precisionVSLoss of time

Solution Approach 1:

The merge operation is divided into multiple passes working on different groups of intermediate pages in parallel. Each pass processes a subset of pages independently, allowing parallel execution while progressively eliminating row ID gaps across the entire table.

Inventive Principle:

Principle #1Segmentation

Solution Approach 2:

Each merge pass performs partial merging on specific groups of pages rather than attempting to merge all pages completely in one pass. This partial action approach allows parallel processing of different page groups, reducing overall processing time while achieving the same contiguity result.

Inventive Principle:

Principle #16Partial or excessive action

Data Source

PatentUS9558221B2Multi-pass, parallel merge for partitioned intermediate pages

Publication Date: 2017.01.31 SYBASE INC

AI summary

Multi-pass parallel merging in a database includes identifying characteristics of non-final pages during database query operations. A phase of page consolidation is triggered based on the identified characteristics and a final page is stored.