Hierarchical Fragment Indices for Database Query Optimization
Find Innovative SolutionsGenerate Solutions
Solution Overview
Problem
Database management systems face challenges in efficiently managing large data sets due to increasing data capacity requirements, where existing methods struggle with blocking situations during merge processes and inefficient indexing, especially in hybrid in-memory and on-disk systems.
Innovation Solution
The approach involves splitting large tables into main part fragments of defined maximum size, assigning relative ages, and creating fragment-specific and multi-fragment index segments for efficient lookup and data management, allowing for on-demand paging and optimized compression, as well as hierarchical indexing to reduce storage costs and improve query performance.
Engineering Contradictions & Design Principles
Engineering Contradiction Analysis
1Quantity of substance
If a single large main part is used to store all data records, then storage efficiency is improved, but query performance and maintenance operations become inefficient due to the inability to perform localized operations
Solution Approach 1:
The patent divides the main part into multiple main part fragments (MPF0, MPF1, MPF2, etc.), each storing a subset of data records. This segmentation enables localized query operations on specific fragments rather than scanning the entire main part, significantly improving query execution efficiency while maintaining efficient storage utilization across all fragments.
2Reliability
If a traditional single index structure is used for the entire main part, then lookup capability is provided, but the index becomes too large to fit in memory and maintenance operations block query processing
Solution Approach 1:
The patent creates fragment-specific index segments (FIS0, FIS1, FIS2, etc.) for each main part fragment, with each index segment containing only the row indices for that specific fragment. This segmentation reduces the size of individual index structures, allowing them to fit in memory, while maintaining complete lookup capability for the entire main part by combining results from multiple fragment-specific index segments.
3Stability of the object's composition
If the main part is updated continuously without fragmentation, then data consistency is maintained, but merge operations block query processing and cause downtime
Solution Approach 1:
By dividing the main part into multiple fragments, the patent enables parallel merge operations on different fragments simultaneously. Query processing can continue on fragments that are not currently being merged, eliminating the need to block all queries during merge operations. This segmentation allows merge operations to be performed incrementally without causing system-wide downtime.
Solution Approach 2:
The patent implements dynamic fragment management where fragments can be opened and closed independently based on merge operation status. When a fragment is being merged, it is closed to prevent modifications, while other fragments remain open and accessible for query processing. This dynamic state management maintains data consistency while allowing continuous system operation.
4Speed
If the entire main part is loaded into memory for processing, then query speed is improved, but memory consumption becomes prohibitive for large data sets
Solution Approach 1:
The patent enables loading only the required main part fragment(s) into memory based on the query requirements, rather than loading the entire main part. Since each fragment contains a subset of data records, this selective loading significantly reduces memory consumption while maintaining fast query processing speed for the relevant data subset.
Data Source
Figure 1
Figure 2
Figure 3
AI summary
Data records of a data set can be stored in multiple main part fragments, each of which includes a subset of the set of data records. A relative age can be assigned to each main part fragment, and a fragment-specific index segment can be created for a newest of the main part fragments. The fragment-specific index segment can provide a lookup ability for logical identifiers of data records in just the newest of the main part fragments. A multi-fragment index segment can span two or more older main fragments. The multi-fragment index segment can provide a lookup ability for logical identifiers of data records in the two or more older main part fragments.