Splitting Database Join Operations for Parallel Query Execution
Find Innovative SolutionsGenerate Solutions
Solution Overview
Problem
Join operations in star and snowflake schemas are time and processor-intensive due to the large size of fact tables, leading to inefficient query processing in databases.
Innovation Solution
The solution involves reordering dimension tables by their restrictiveness and splitting join operations into probe and build operations to enable parallelization, allowing the build operation to be performed independently on each dimension table, thereby reducing the overall number of operations and improving execution time.
Engineering Contradictions & Design Principles
Engineering Contradiction Analysis
1Reliability
If join operations are performed on large fact tables in star schema, then query results are obtained, but the operation becomes time and processor-intensive
Solution Approach 1:
The patent divides the join operation into two separate phases: a build phase where a hash table is constructed from one table, and a probe phase where the other table is scanned and matched against the hash table. This segmentation allows the join operation to be performed more efficiently by avoiding nested loop comparisons, thereby reducing execution time while maintaining complete query processing
Solution Approach 2:
The build phase is performed as a preliminary action before the probe phase, where data from one table is pre-processed into a hash table structure. This preliminary organization of data enables faster matching during the probe phase, significantly reducing the overall join operation time while ensuring all necessary data is prepared for complete query results
2Reliability
If join operations are performed on large fact tables in star schema, then query results are obtained, but processor intensity increases
Solution Approach 1:
By segmenting the join into build and probe phases with distinct processor roles, the patent reduces overall processor intensity. The build phase creates a lookup structure that enables the probe phase to process data with fewer computational operations, thereby maintaining query completeness while lowering the power requirements for join operations
3Reliability
If dimension tables are processed in sequential order, then join operations are completed, but execution time increases
Solution Approach 1:
The build phase serves as a preliminary action that prepares data structures in advance, enabling the probe phase to execute quickly. This preliminary organization of data into hash tables allows for rapid lookups during probing, significantly improving query execution speed while ensuring all joins are completed accurately
Data Source
AI summary
A system and method for processing a database query is described. In response to detection that a database query involves a star or snowflake join operation, a join operator in a preliminary query plan can be split into a build operator and a probe operator. The probe operator can be placed in a final query plan in the same place as the join operator in the preliminary query plan, while the build operator can be placed beneath the probe operator in the final query plan, between an exchange operator and the exchange operator's child from the preliminary query plan.