Value-ID Sorting in Column-Store Databases
Find Innovative SolutionsGenerate Solutions
Solution Overview
Problem
Existing approaches to sorting dictionary-compressed values in column-store databases are inefficient, leading to slow performance in handling sort operations.
Innovation Solution
Implementing a value-ID-based sorting method that utilizes a dictionary structure and an inverted index structure to efficiently sort values by mapping distinct values to corresponding value IDs, allowing for rapid and efficient sort operations through optimized position lookup and NULL handling phases.
Engineering Contradictions & Design Principles
Engineering Contradiction Analysis
1Quantity of substance
If dictionary compression is used to reduce memory usage, then storage efficiency is improved, but sort operation performance deteriorates
Solution Approach 1:
The patent pre-computes and stores position lists for each value ID in the inverted index structure before sorting is needed. When a sort operation is requested, the system simply retrieves these pre-computed position lists and merges them, avoiding the need to scan and compare compressed dictionary values during the sort operation. This preliminary preparation resolves the contradiction by maintaining compression while enabling fast sorting.
Solution Approach 2:
The patent introduces an inverted index structure as an intermediary data structure that maps value IDs to position lists. This intermediary layer allows the system to work with compact value IDs instead of full dictionary values during sorting, while still maintaining the ability to retrieve original values when needed. The inverted index acts as a mediator that preserves both compression benefits and sort performance.
2Device complexity
If existing sort approaches are used on dictionary-compressed values, then implementation simplicity is maintained, but processing speed deteriorates
Solution Approach 1:
The patent segments the sorting process into distinct phases: retrieving position lists from the inverted index, merging these position lists based on value ID order, and handling NULL values separately. This segmentation allows each phase to be optimized independently, significantly improving processing speed while keeping the overall implementation manageable through clear separation of concerns.
Solution Approach 2:
The patent changes the fundamental parameter being sorted from dictionary-compressed values to value IDs with their associated position lists. By transforming the sort operation from comparing actual values to merging pre-organized position lists based on value ID order, the system achieves dramatic speed improvements while the inverted index structure maintains implementation organization.
Data Source
Figure 1~6
Figure 2~3
Figure 4
AI summary
Innovations in performing sort operations for dictionary-compressed values of columns in a column-store database using value identifiers ("IDs") are described. For example, a database system includes a data store and an execution engine. The data store stores values at positions of a column. A dictionary maps distinct values to corresponding value IDs. An inverted index stores, for each of the corresponding value IDs, a list of those of the positions that contain the associated distinct value. The execution engine processes a request to sort values at an input set of the positions and identify an output set of the positions for sorted values. In particular, the execution engine iterates through positions stored in the lists of the inverted index. For a given position, the execution engine checks if the given position is one of the input set and, if so, adds the given position to the output set.