Sorted Dictionary Access for Column-Oriented Database Queries
Find Innovative SolutionsGenerate Solutions
Solution Overview
Problem
Database systems face challenges in optimizing performance for complex queries and high transaction volumes, particularly in sorting and scanning operations, which consume significant CPU resources and expose inefficient memory access patterns.
Innovation Solution
The method involves detecting query operators that require sorting or scanning in column-oriented databases and exploiting the columnar value-ID based data layout to directly access sorted dictionaries, avoiding explicit sorting and scanning by checking for eligible sorted dictionary access and using inverted indexes and search trees to retrieve sorted values efficiently.
Engineering Contradictions & Design Principles
Engineering Contradiction Analysis
1Measurement precision
If sorting algorithms are executed to satisfy ORDER BY, LIMIT, MIN, and MAX query operators, then query results are obtained correctly, but CPU resources are consumed significantly and processing time increases
Solution Approach 1:
The patent pre-sorts column data during the data loading phase and maintains sorted dictionaries in memory. When queries arrive, the pre-sorted data structure allows direct access to sorted values without executing sorting algorithms, thus resolving the contradiction between obtaining correct sorted results and minimizing CPU consumption and processing time
Solution Approach 2:
The patent creates a sorted dictionary (a copy of the column data in sorted order) during data loading. This copy stores both the sorted values and their corresponding row positions, allowing query operators to access sorted data directly without re-sorting, thereby achieving fast query processing while maintaining result accuracy
2Loss of information
If full table scanning is performed to satisfy query operators, then all required data is retrieved, but memory access patterns become inefficient and processing load increases
Solution Approach 1:
The patent segments the data access process by using the sorted dictionary structure to directly access only the specific rows needed for query operators. Instead of scanning the entire table sequentially, the system jumps directly to relevant row positions stored in the sorted dictionary, reducing memory access overhead while ensuring complete retrieval of required data
Solution Approach 2:
The sorted dictionary acts as an intermediary structure between the raw column data and query operators. It stores pre-computed row positions for sorted values, allowing the system to access specific rows efficiently without full table scanning, thus improving memory access patterns while maintaining data retrieval completeness
Data Source
AI summary
In some example embodiments, there may be provided a method including detecting a query operator of a predetermined type requiring a sorting or a scanning responsive to the query operator at a column-oriented database; determining a sorted dictionary at the column-oriented database is eligible for direct access by at least checking that the column-oriented database provides sorted dictionary access and that dictionary collation and a collation of the query operator are the same; when the sorted dictionary is eligible, reading directly from the sorted dictionary a batch of sorted values; and processing, based on the batch sorted values read from the sorted dictionary, the query operator.