Virtual Dictionary Columns for Faster String Query Execution
Find Innovative SolutionsGenerate Solutions
Solution Overview
Problem
Columnar operations, particularly string operations on character data types in large-scale databases, are inefficient and time-consuming due to the need for repeated calculations on individual rows, especially in column-based storage systems.
Innovation Solution
Generate a virtual column by applying a structured query language operation on distinct values, populate it with precalculated values, and compress it using dictionary-based compression to create a column of value identifiers, enabling efficient retrieval of results from the compressed virtual column.
Engineering Contradictions & Design Principles
Engineering Contradiction Analysis
1Productivity
If string functions are implemented in SQL for character data types in column-based storage, then queries can be executed, but the operations become expensive and time-consuming
Solution Approach 1:
The patent applies preliminary action by pre-calculating string operations on distinct values and storing the results in a virtual column before actual queries are executed. This allows repeated queries to retrieve precalculated values directly without performing expensive string operations again, significantly reducing query execution time.
Solution Approach 2:
The patent creates a virtual column that copies the results of string operations on distinct values. Instead of performing operations on the original column data during each query, the system uses the precalculated values stored in the virtual column, effectively copying the computation results to avoid repeated expensive operations.
2Quantity of substance
If calculations are executed on individual columns for columnar operations, then operations can be performed on large-scale data, but the calculations become inefficient and time-consuming
Solution Approach 1:
The system performs calculations in advance on distinct values and stores them in a virtual column. When queries are executed, the precalculated values are retrieved directly, avoiding the need to perform expensive calculations on large-scale data repeatedly, thus improving calculation efficiency while maintaining the ability to handle large data volumes.
Solution Approach 2:
The patent extracts the calculation operation from the query execution process and separates it into a pre-processing step. By extracting the string operations and performing them on distinct values beforehand, the system eliminates the need to repeat these expensive calculations during actual query processing, improving overall efficiency.
3Ease of operation
If repeated calculations are performed on individual rows, then queries can be answered, but the operations become expensive and inefficient
Solution Approach 1:
The system performs calculations in advance on distinct values and stores the results in a virtual column. This preliminary action eliminates the need for repeated calculations on individual rows during query processing, significantly reducing computational resource consumption while maintaining ease of query operation.
Solution Approach 2:
The patent creates a virtual column that copies precalculated values from distinct values. This allows the system to retrieve results without performing repeated calculations, reducing energy consumption while maintaining operational ease for query processing.
Data Source
AI summary
Arrangements for a reading scheme for column-oriented databases are provided. A virtual column may be generated by applying a structured query language operation on distinct values in a column of data in a table. Based on applying the structured query language operation, the virtual column may be populated with corresponding precalculated values. The virtual column may be compressed with dictionary-based compression. The compressing may include generating a column of value identifiers, each of the value identifiers representing a distinct value in the column of data. A database query against the compressed virtual column may be received. A result of the database query may be returned by retrieving one or more of the precalculated values from the compressed virtual column.