Database Join Plan Compression for Intermediate Table Size Reduction
Find Innovative SolutionsGenerate Solutions
Solution Overview
Problem
The use of Cartesian products in join plans for relational database management systems often results in large intermediate tables, requiring significant random access memory and secondary storage, leading to performance bottlenecks due to the large amount of data that needs to be transferred and processed.
Innovation Solution
Identifying compressible columns in database tables, applying data compression techniques to reduce the size of data values, and storing the results of the join operation in an intermediate table, thereby reducing storage and bandwidth requirements.
Engineering Contradictions & Design Principles
Engineering Contradiction Analysis
1Reliability
If Cartesian products are used to join database tables, then complete join results are obtained, but intermediate table size becomes extremely large
Solution Approach 1:
The patent extracts only the essential information needed for the join operation by identifying compressible columns and their unique values, rather than materializing the complete Cartesian product. This extraction approach retrieves minimal necessary data (unique values and their frequencies) to reconstruct join results, dramatically reducing intermediate table size while preserving join completeness.
Solution Approach 2:
The patent creates a compressed representation (copy) of the intermediate table data by storing only unique values and their occurrence frequencies in compressible columns, rather than storing every row of the full Cartesian product. This copying strategy maintains the ability to reconstruct complete join results while using minimal storage space.
2Reliability
If large intermediate tables are created, then all join data is available, but memory and storage resources are consumed
Solution Approach 1:
The patent changes the data representation parameters by applying compression transformations to identify and encode only unique values and their frequencies in compressible columns. This parameter transformation allows the system to maintain data availability for joins while dramatically reducing the memory and storage parameters required to hold intermediate results.
Solution Approach 2:
The patent performs preliminary analysis to identify compressible columns and their unique values before executing the join operation. This preliminary action prepares a compressed data structure that will suffice for the join, preventing the need to allocate resources for large intermediate tables during the actual join execution.
3Reliability
If large intermediate tables are transferred over communication bus, then complete data is transmitted, but transfer time increases
Solution Approach 1:
The patent extracts only the essential unique values and frequency information from compressible columns for transmission over the communication bus, rather than transmitting complete intermediate table data. This extraction ensures that all necessary information for reconstructing join results is transmitted while minimizing transfer time by reducing data volume.
4Quantity of substance
If compression is applied to reduce intermediate table size, then storage requirements decrease, but processing complexity increases
Solution Approach 1:
The patent performs preliminary identification of compressible columns and their unique values using value-count join indexes or counting techniques before the join operation. This preliminary compression setup, while adding some processing steps, establishes a compact data structure that simplifies subsequent join operations by working with reduced data sizes and pre-organized unique value information.
Data Source
AI summary
A join plan creates an intermediate table from two or more database tables within a database. In doing so, at least one projected and compressible column within one of the database tables is identified, where the column contains data values to which the application of a compression data transformation results in a reduction in the size of the data values. The frequency of occurrences of at least some unique values in the compressible column is identified, and then the two or more database tables are joined. The results of the join are then stored in the intermediate table such that the compression data transformation is applied to at least some of the unique values in the compressible column for which the frequency has been identified.