Hierarchical Database Index Partitioning for Write-Once Tables

Resolve Bottlenecks,
Find Innovative SolutionsGenerate Solutions

Solution Overview

Problem

Relational database management systems face performance degradation when dealing with extremely large database indices, particularly due to increased secondary storage access times and locking issues during insert operations, especially in applications with high data volumes like RFID systems, where traditional indexing methods lead to high I/O loads and slow read operations.

Innovation Solution

Implementing a database index with a hierarchy of partitions of exponentially increasing size, where each level's index partition is stored in memory until it reaches a predetermined size, then moved to secondary storage and merged into larger partitions, reducing the number of partitions and improving read and write performance.

Engineering Contradictions & Design Principles

VSEngineering Contradiction Analysis

1Speed

If a traditional database index is used to speed up data selection, then read operation performance is improved, but insert operation performance degrades due to increased I/O load and locking issues

Engineering Contradiction:

Improveread operation speedVSAvoidinsert operation speed

Core Design Contradiction:

SpeedVSProductivity

Solution Approach 1:

The database index is divided into multiple partitions, each maintained separately. This segmentation allows read operations to be distributed across partitions while insert operations can be performed on individual partitions without locking the entire index, thus improving both read and insert performance simultaneously

Inventive Principle:

Principle #1Segmentation

2Quantity of substance

If the index size grows to accommodate more data, then data capacity increases, but access performance degrades due to increased secondary storage access requirements

Engineering Contradiction:

Improvedata capacityVSAvoidindex access speed

Core Design Contradiction:

Quantity of substanceVSSpeed

Solution Approach 1:

The index structure transitions from a single flat index to a hierarchical multi-level partitioned structure. This dimensional change allows the system to accommodate large data volumes while maintaining access performance by organizing data in a tree-like hierarchy where not all levels need to be accessed for every operation

Inventive Principle:

Principle #17Another dimension (Dimensionality change)

3Speed

If a fixed-size data cache is used to store index pages in memory, then memory access speed is maintained, but the index cannot grow beyond cache size without performance degradation

Engineering Contradiction:

Improvememory access speedVSAvoidindex size adaptability

Core Design Contradiction:

SpeedVSAdaptability or versatility

Solution Approach 1:

The system implements dynamic index partitioning where the number and size of index partitions can grow and shrink based on data volume and cache capacity. This allows the index structure to adapt to changing data sizes while maintaining the performance benefits of cache-resident partitions

Inventive Principle:

Principle #15Dynamics

Data Source

PatentUS9672235B2Method and system for dynamically partitioning very large database indices on write-once tables

Publication Date: 2017.06.06 SAP SE

AI summary

Methods and systems for partitioning and dynamically merging a database index are described. A database index includes a single first-level index partition stored in a data cache. As the first-level index partition in the data cache reaches a predetermined size, it is copied to secondary storage and a new index partition is generated in the data cache. When the number of index partitions in secondary storage reaches some predetermined number, the index partitions are merged to create a single index partition of a higher level in a hierarchy of index partitions having an exponentially increasing size with each increase in level within the hierarchy.