Database Index Page Split Avoidance via Key Relocation

Resolve Bottlenecks,
Find Innovative SolutionsGenerate Solutions

Solution Overview

Problem

Relational databases face inefficiencies and high costs due to frequent page splits when inserting large numbers of rows, leading to complex and time-consuming index key insertions and database reorganizations.

Innovation Solution

A method that identifies suitable pages in a key-ordered chain of index pages to move existing keys and make room for new keys, reducing the need for page splits by rearranging keys between adjacent pages and using caching to manage high-key values, thereby maintaining efficient leaf-page chains and minimizing non-leaf page splits.

Engineering Contradictions & Design Principles

VSEngineering Contradiction Analysis

1Productivity

If traditional page insertion methods are used, then data can be inserted into the database, but page splits occur frequently causing high processing costs and complex leaf-page chains

Engineering Contradiction:

Improveinsertion efficiencyVSAvoidleaf-page chain complexity

Core Design Contradiction:

ProductivityVSDevice complexity

Solution Approach 1:

The system performs preliminary actions by examining the next page in the leaf-page chain before inserting a key. If the target page is full, the system proactively checks whether the next page has available space and performs key movements in advance to prevent page splits, thereby improving insertion efficiency and maintaining simple leaf-page chains.

Inventive Principle:

Principle #10Preliminary action

2Adaptability or versatility

If page splits are performed to accommodate new keys, then insertion capacity is maintained, but processing time and I/O operations increase

Engineering Contradiction:

Improveinsertion capacityVSAvoidprocessing time

Core Design Contradiction:

Adaptability or versatilityVSLoss of time

Solution Approach 1:

The system extracts keys from the target page and moves them to the next page in the leaf-page chain when the target page is full. This extraction approach avoids the need for traditional page splits, reducing processing time and I/O operations while maintaining the database's ability to accommodate new keys.

Inventive Principle:

Principle #2Taking out (Extraction)

3Device complexity

If keys are moved to adjacent pages to avoid page splits, then page split frequency decreases, but additional processing operations are required

Engineering Contradiction:

Improvepage split frequencyVSAvoidinsertion operation complexity

Core Design Contradiction:

Device complexityVSEase of manufacture

Solution Approach 1:

The system applies local quality by performing key movements only when and where needed in the leaf-page chain. Instead of globally restructuring the index, the system locally examines the next page and performs targeted key movements only for full pages, thereby reducing page split frequency while keeping insertion operations relatively simple.

Inventive Principle:

Principle #3Local quality

Data Source

PatentUS8682872B2Index page split avoidance with mass insert processing

Publication Date: 2014.03.25 BMC SOFTWARE INC

AI summary

A technique is disclosed that avoids index page splits when inserting large numbers of rows into a table of a relational database. Keys in index pages are moved to successive index pages to make room to insert keys on the original index page. Where no room is available on successive pages, a new index page is created to hold moved keys. The result is typically a smaller chain of index pages with better locality than using the conventional insertion technique of splitting index pages.