Outer Join Optimization via Predicate Flag Segmentation
Find Innovative SolutionsGenerate Solutions
Solution Overview
Problem
Current query optimizers cannot apply Partial GROUP BY (PGB) optimization to outer joins effectively because it leads to the loss of information about which rows satisfy or do not satisfy the ON clause predicate, especially in cases where rows with the same join attribute values and grouping-list values are aggregated prematurely.
Innovation Solution
Two novel techniques are introduced to optimize outer joins by rewriting queries to allow PGB application: the first technique uses a case expression to differentiate rows that satisfy or do not satisfy the ON clause predicate, and the second technique involves parallel processing and redistribution of rows to maintain predicate satisfaction information during aggregation.
Engineering Contradictions & Design Principles
Engineering Contradiction Analysis
1Productivity
If Partial GROUP BY is applied to the outer table in an outer join, then query performance is improved by reducing row redistribution cost and input sizes, but un-matching rows of the outer table are lost after early aggregation
Solution Approach 1:
The outer table rows are segmented into two distinct groups: matching rows and un-matching rows. This segmentation is achieved by introducing a flag column that identifies whether each row has a matching counterpart in the joined table. By separating these rows into different aggregation groups, the patent ensures that un-matching rows are preserved and can be properly handled in the final result set, while still allowing PGB optimization to be applied to matching rows for performance improvement.
Solution Approach 2:
A flag column acts as an intermediary mechanism between the early aggregation process and the final result production. This flag stores information about predicate satisfaction (whether rows match), allowing the aggregation to proceed early on numeric attributes while preserving the matching status information. The flag serves as a mediator that bridges the gap between optimized aggregation and correct outer join semantics, ensuring un-matching rows are not lost.
2Productivity
If rows with the same join attribute values and grouping-list values are aggregated prematurely, then processing efficiency is improved, but information about predicate satisfaction is lost
Solution Approach 1:
The patent performs preliminary aggregation on numeric attributes before the final join operation, but does so in a way that preserves predicate satisfaction information. By introducing the flag column and including it in the GROUP BY clause, the aggregation is performed in advance on the numeric columns while the flag column maintains the matching status. This preliminary action speeds up processing by reducing the amount of data that needs to be handled in later stages, while the flag ensures predicate satisfaction information is not lost.
Data Source
AI summary
Techniques for optimizing outer joins in database operations are provided. In an embodiment, a query having an outer join with a GROUP BY clause is rewritten and expanded to expand a first level partition of that GROUP BY clause to produce a modified query. In another situation, rows associated with processing a query are each row split and then hashed based at least in part on attributes of the outer join predicate. A left outer join is performed and a global aggregation processed to produce a spooled table to process the query.