Piyush Goel

Efficient processing of outer joins and aggregate junctions

Removal of redundant outer joins is essential for the reassociation of outer joins with other binary operations. We present a set of comprehensive algorithms that employ the properties of strong predicates along with the properties of aggregation, intersection, union, and except operations to remove redundant outer joins from a query. For the purpose of query simplification, we generate additional projections by determining the keys. Our algorithm for generating keys is based on a novel concept of weak bindings that is essential for queries containing outer joins. Our algorithm for converting outer joins to joins is based on a novel concept of join-reducibility.

SQL query optimization: reordering for a general class of queries

The strength of commercial query optimizers like DB2 comes from their ability to select an optimal order by generating all equivalent reorderings of binary operators. However, there are no known methods to generate all equivalent reorderings for a SQL query containing joins, outer joins, and groupby aggregations. Consequently, some of the reorderings with significantly lower cost may be missed. Using hypergraph model and a set of novel identities, we propose a method to reorder a SQL query containing joins, outer joins, and groupby aggregations. While these operators are sufficient to capture the SQL semantics, it is during their reordering that we identify a powerful primitive needed for a dbms. We report our findings of a simple, yet fundamental operator, generalized selection, and demonstrate its power to solve the problem of reordering of SQL queries containing joins, outer joins, and groupby aggregations.