Three-Step Join Processing for Column Partitioned Tables
Find Innovative SolutionsGenerate Solutions
Solution Overview
Problem
Conventional join operations on column-partitioned database tables incur unnecessary I/O costs due to the spooling of all columns, even when only some columns are needed for the join, leading to inefficient query performance, especially when large tables are involved and cannot be duplicated for direct access.
Innovation Solution
A three-step join processing method is introduced, where only the columns needed for the join are spooled, followed by a rowid join to produce temporary tables, and finally, the remaining columns are accessed to generate the results table, allowing the query optimizer to cost and optimize each step for improved performance.
Engineering Contradictions & Design Principles
Engineering Contradiction Analysis
1Productivity
If the column-partitioned table is spooled for join operation, then the join can be performed, but all columns are read including non-join columns which incurs unnecessary I/O
Solution Approach 1:
The patent segments the column-partitioned table into two distinct parts: join columns and non-join columns. The join columns are spooled to temporary storage for the join operation, while non-join columns remain in their original location and are accessed only after the join condition is satisfied. This segmentation eliminates unnecessary I/O by reading only the essential join columns during the spooling phase.
Solution Approach 2:
The patent extracts and separates the join columns from the non-join columns in the column-partitioned table. By taking out only the join columns needed for the operation and leaving the rest behind, the system avoids the inefficiency of reading and writing all columns to temporary storage, thereby reducing I/O costs while maintaining join functionality.
2Loss of energy
If the other table is duplicated for direct join, then I/O reduction is achieved for small qualifying rows, but the table must be large enough to be duplicatable
Solution Approach 1:
The patent segments the join processing into two phases: first spooling only join columns from the column-partitioned table to temporary storage, then performing the join with the other table. This segmentation allows the approach to work efficiently with large tables that cannot be fully duplicated, as only essential columns are moved to temporary storage.
3Ease of operation
If conventional row-based join processing is used, then simplicity is maintained, but column-level optimization options are not utilized
Solution Approach 1:
The patent segments the table access pattern by first reading only join columns from the column-partitioned table, performing the join operation, and then accessing non-join columns only for qualifying rows. This segmentation enables column-level optimization while maintaining a relatively simple processing framework that builds upon conventional join operations.
Data Source
AI summary
Techniques for processing joins on column partitioned tables are provided. A query includes a first-Column Partition (CP) table joined with a second-CP table. The query is decomposed into a three-step process and rewritten and processed.