Database Record Reordering for Column Compression
Find Innovative SolutionsGenerate Solutions
Solution Overview
Problem
Query performance in data warehouses is limited by sub-optimal compression and scan performance in relational databases, where records are typically compressed in the order of receipt or generation, leading to inefficient compression and increased computational workload.
Innovation Solution
Prioritize and sort data records based on their impact to a performance metric, such as anticipated usage and cost functions, to optimize compression and reduce computational time in column-oriented databases, using techniques like Run-Length Encoding, Delta Encoding, and Prefix Encoding.
Engineering Contradictions & Design Principles
Engineering Contradiction Analysis
1Quantity of substance
If records are compressed in the order of receipt or generation, then storage requirements are reduced, but compression efficiency is sub-optimal and scan performance is limited
Solution Approach 1:
The patent applies preliminary action by reordering records into sorted sequences before compression. The system identifies high-cardinality columns and sorts records based on those columns prior to applying compression algorithms, ensuring that similar values are grouped together. This preprocessing step enables more effective compression by creating patterns that compression algorithms can exploit, thereby improving compression efficiency without increasing storage requirements.
2Productivity
If records are reordered to optimize compression, then compression efficiency improves, but computational workload increases
Solution Approach 1:
The patent applies local quality by focusing computational effort selectively rather than uniformly across all data. The system identifies high-cardinality columns that have the greatest impact on compression efficiency and prioritizes sorting on those columns. By concentrating computational resources on the most impactful columns rather than sorting all columns equally, the system achieves improved compression efficiency while minimizing the increase in computational workload.
Solution Approach 2:
The patent applies parameter changes by dynamically adjusting sorting priorities based on column characteristics. The system evaluates columns based on their cardinality and impact on compression, then adjusts the sorting strategy accordingly. High-cardinality columns that provide the greatest compression benefit are sorted first, while low-cardinality columns may be skipped or given lower priority, thereby optimizing the balance between computational effort and compression gain.
3Speed
If user-directed sorting is applied on a particular column, then scan performance improves, but device complexity increases
Solution Approach 1:
The patent applies self-service by enabling the system to automatically identify and prioritize columns for sorting based on their inherent characteristics. Rather than requiring users to manually specify which columns to sort on, the system autonomously evaluates columns based on cardinality and compression impact, then automatically applies sorting to the most beneficial columns. This automation reduces system complexity by eliminating the need for complex user interfaces and configuration management while maintaining improved scan performance.
Data Source
AI summary
According to embodiments of the present invention, apparatus, systems, methods and computer program products for sorting and compressing an unordered set of data records from a structured database are provided. Fields of the unordered set of data records are prioritized based on an impact of those fields to a compression scheme for column-oriented compression. The unordered set of data records are sorted based on the prioritized field(s) with a greatest impact on the performance metric. Data of the sorted data records are compressed according to a compression scheme. In some embodiments, prioritizing the fields may be based on an anticipated level of usage of data within those fields and/or a cost function associated with a performance metric as well as optimization of compression. A performance metric may include a faster computational time, reduced I/O computation, faster scan time, etc.