The strength of commercial query optimizers like DB2 comes from their ability to select an optimal order by generating all equivalent reordering of binary operators. However, there are no known methods to generate all equivalent reordering for a SQL query containing joins, outer joins, and groupby aggregations. Consequently, some of the reordering 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 cent

more »

... ing 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. expressions can be reorder by employing MGO J described in [BHAR95a].