Per-Segment Secondary Indexing for Heterogeneous Data Retrieval
Find Innovative SolutionsGenerate Solutions
Solution Overview
Problem
Existing database systems face limitations in processing speed due to hardware constraints, data storage methods, and restricted co-processing options, leading to inefficiencies in data retrieval and query execution.
Innovation Solution
Implementing a per-segment secondary indexing strategy that allows for the selection and generation of different secondary indexing schemes for each segment based on local data distribution, enabling efficient access and retrieval of data through optimized indexing for heterogeneous data distributions over time.
Engineering Contradictions & Design Principles
Engineering Contradiction Analysis
1Device complexity
If a single secondary indexing scheme is used for all segments, then the system structure is simple and easy to manage, but query execution performance deteriorates when data distribution varies across segments
Solution Approach 1:
The patent divides the database into multiple segments and applies different secondary indexing schemes to different segments based on their local data distribution characteristics. This segmentation allows each segment to have an indexing strategy optimized for its specific data patterns, thereby improving overall query execution performance without requiring a complex global indexing structure.
Solution Approach 2:
The patent implements local quality by selecting secondary indexes tailored to each segment's specific data distribution. Instead of applying a uniform indexing approach across all segments, the system analyzes local data characteristics and applies appropriate indexing schemes (such as different column selections or index types) to each segment, optimizing query performance for heterogeneous data distributions.
2Productivity
If different secondary indexing schemes are implemented for different segments, then query execution performance is improved for heterogeneous data distributions, but the system complexity and difficulty of management increase
Solution Approach 1:
The patent introduces dynamic index selection that adapts to changing data distributions over time. The system can identify when data distribution patterns change and automatically adjust or rebuild secondary indexes accordingly, allowing the indexing structure to evolve dynamically rather than remaining static, thus maintaining optimal query performance without manual intervention.
Solution Approach 2:
The patent implements self-service mechanisms where the database system automatically identifies segments with changing data distributions and triggers index reconstruction or optimization without external intervention. This self-managing capability reduces the operational burden on database administrators while maintaining high query execution performance across diverse data segments.
3Ease of manufacture
If data is stored in a fixed manner to simplify storage management, then storage operations are efficient and simple, but retrieval speed deteriorates when query patterns vary
Solution Approach 1:
The patent applies preliminary action by pre-computing and maintaining multiple secondary indexes on data segments during the storage phase. Instead of waiting for query execution to determine the best access path, the system proactively creates and maintains indexed structures in advance, enabling fast retrieval regardless of the specific query patterns that emerge later, thus balancing storage overhead with retrieval performance.
Data Source
AI summary
A database system is operable to generate a first segment that includes a first subset of a plurality of rows. A first secondary indexing scheme for indexing the first segment is selected from a plurality of secondary indexing options, and first index data for the first segment is generated in accordance with the first secondary indexing scheme. A second segment is generated that includes a second subset of the plurality of rows. A second secondary indexing scheme for indexing the second segment is selected from the plurality of secondary indexing options, and second index data is generated for the second segment in accordance with the second secondary indexing scheme. Execution of a query is facilitated by utilizing the first index data and the second index data.