Unified Database Index Structure for Multi-Column Predicate Search
Find Innovative SolutionsGenerate Solutions
Solution Overview
Problem
Database index operations, such as B-tree lookups and record ID sorting, can slow down query performance and increase costs, especially during online transaction processing (OLTP) and extract, transform, and load (ETL) processes, due to the need for frequent index updates and multiple index accesses.
Innovation Solution
A data structure is implemented that stores column attributes, including a record identification (RID) column and predicate columns, using bit string or integer encoding to partition the data for efficient evaluation, reducing the need for multiple indexes and optimizing query performance by decoupling predicate search into separate partitions.
Engineering Contradictions & Design Principles
Engineering Contradiction Analysis
1Speed
If traditional B-tree indexes are used for database table look-up, then query speed can be improved for single-column predicates, but multiple indexes are needed for multi-column predicates and the system requires frequent index updates that slow down OLTP workloads
Solution Approach 1:
The patent combines multiple index columns into a single sorted data structure where all predicate columns are stored together in sorted order. This eliminates the need for separate B-tree indexes for each column or combination, reducing index management complexity while maintaining fast query performance through a unified sorted structure that supports efficient binary search operations.
Solution Approach 2:
The sorted data structure serves multiple functions simultaneously: it acts as an index for single-column predicates, supports multi-column predicates without requiring additional indexes, and provides fast lookup for various predicate combinations. This universal structure replaces the need for multiple specialized indexes, reducing the overhead of index creation and maintenance.
2Adaptability or versatility
If multiple B-tree indexes are created to satisfy different predicate variations, then query flexibility is improved, but the number of index updates increases during data insertion and modification
Solution Approach 1:
The patent merges all predicate column data into a single sorted structure that handles all predicate variations simultaneously. When data is inserted or modified, only one sorted structure needs to be updated instead of multiple separate indexes, thereby improving OLTP processing speed while maintaining the ability to handle diverse predicate queries through the unified structure.
Solution Approach 2:
The single sorted data structure provides universal support for all predicate combinations, making the system adaptable to different query types without requiring additional indexes. This multi-functional approach ensures that flexibility in handling various predicates is maintained while reducing the productivity overhead associated with updating multiple indexes during transaction processing.
3Speed
If traditional indexes are used, then fast lookup is achieved for leading columns, but non-leading columns require index scanning and multiple RID sorts
Solution Approach 1:
The patent applies local quality by ensuring that all columns within the sorted structure are treated equally and stored in sorted order, unlike traditional indexes where only the leading column is sorted. This allows any column (whether it would traditionally be leading or non-leading) to serve as an efficient search key, eliminating the need for index scanning and multiple RID sorts when querying non-leading columns.
Data Source
AI summary
Techniques for database table look-up are provided. The techniques include storing one or more column attributes of a database table in a data structure, wherein the data structure also comprises a record identification (RID) column of a table, one or more predicate columns corresponding to the RID column, and a sequence number column that is associated with one or more updated records, generating a key using one or more portions from one or more of the one or more predicate columns, using the key to partition the data structure, wherein partitioning the data structure comprises partitioning the one or more predicate columns for evaluation, and evaluating the one or more predicate columns against the data structure for each matching predicate column-data structure partition.