Techniques for optimizing outer joins
AssigneeTeradata US, Inc.
InventorsYu Xu, Ahmad Said Ghazal
Life of this patent
Estimating the term…
Abstract
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.
Claims20
A method implemented and programmed within a non-transitory computer-readable storage medium and processed by one or more processors, the processors configured to execute the method, comprising:
- detecting in a query an outer join on a join table and a second table, the query also including a GROUP BY clause;
- expanding the GROUP BY clause to double a total number of groups in a first level partition of the GROUP BY clause for the second table by generating additional query statements in an expanded query that when executed produce a first list of first attributes that satisfy an ON clause predicate for the second table on an ON clause and a second list of attributes that satisfy a join predicate on the ON clause for the second table;
- and
- producing a modified query having the expanded GROUP BY clause, the modified query is equivalent to the query.
A method implemented and programmed within a non-transitory computer-readable storage medium and processed by one or more processors, the processors configured to execute the method, comprising:
- acquiring a query for processing having an outer join and a GROUP BY clause;
- splitting a first table into a first spool having first rows satisfying an On clause predicate and a second spool having second rows that do not satisfy the ON clause predicate;
- aggregating the first spool on first attributes in a join predicate to create an aggregate spool;
- hashing and redistributing an outer join table on second attributes of the outer join table in the join predicate to create a redistributed outer join spool;
- hashing and redistributing the aggregate spool on first table attributes in the join predicate to create a redistributed first table spool;
- joining the aggregate spool and the redistributed outer join spool and adding results to the second spool;
- and
- applying an aggregation algorithm to the second spool based on the first table attributes and outer join table attributes with final results stored in a final spool table.
A method implemented and programmed within a non-transitory computer-readable storage medium and processed by one or more processors, the processors configured to execute the method, comprising:
- acquiring a query for processing having an outer join and a GROUP BY clause;
- splitting a first table into a first spool having first rows satisfying an On clause predicate and a second spool having second rows that do not satisfy the ON clause predicate;
- hashing and redistributing the second spool according to join predicate attributes to a redistributed second spool;
- aggregating the first spool on first table attributes and a join predicate with results stored in aggregated first table spool;
- hashing and redistributing a join table on join table attributes in the join predicate;
- hashing and redistributing the aggregated first table spool on the first table attributes in the join predicate to create a redistributed first table spool;
- joining the aggregated first table spool and the redistributed first table spool with results stored in a redistributed second spool;
- and
- applying an aggregation algorithm to the redistributed second spool on the join predicate with final results stored in a final spool table.
Dependencies are inferred from the claim text.