Calculated Dictionary Columns for Faster SQL String Operations
Find Innovative SolutionsGenerate Solutions
Solution Overview
Problem
Columnar operations, particularly string operations in structured query language (SQL), are inefficient and time-consuming, especially when dealing with large-scale character data types in column-based storage.
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, generating a column of value identifiers, to optimize database queries.
Engineering Contradictions & Design Principles
Engineering Contradiction Analysis
1Productivity
If string operations are performed on large-scale character data types in column-based storage, then the database can process queries with character data, but the operations become expensive, inefficient, and time consuming
Solution Approach 1:
The patent applies preliminary action by pre-calculating string operation results for all distinct values in a column and storing them in a dictionary structure before queries are executed. When a string operation is needed, the system retrieves pre-computed results from the dictionary rather than performing the operation repeatedly on each row, thus eliminating redundant calculations and significantly reducing query execution time.
2Adaptability or versatility
If calculations are executed on individual columns with formulas applied on each row, then the database can perform columnar operations, but the operations become expensive and inefficient especially when performed on a large scale
Solution Approach 1:
The system performs string operations on distinct values beforehand and stores the results in a dictionary. When queries require these operations, the pre-computed results are retrieved directly, avoiding repeated calculations on every row and significantly improving operation efficiency.
Solution Approach 2:
The patent creates a virtual column that copies the results of string operations from the original column data. This virtual column contains pre-computed values that can be queried without re-executing the original string operations, effectively creating a lightweight copy that maintains queryability while eliminating computational overhead.
3Productivity
If a virtual column is created with precalculated values, then query execution efficiency is improved, but additional storage space is required to store the virtual column and value identifiers
Solution Approach 1:
The patent transforms the storage representation by changing from storing actual string values to storing compact integer value identifiers in the virtual column. The dictionary maps these compact identifiers back to the original string values only when needed for output. This parameter change dramatically reduces the storage space required for the virtual column while maintaining full query functionality.
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.