4.4 Emst Application on One Qgm Box Output: Query Graph G 0 after Applying Emst Rule to Box Example 4.9 Continuing with Example 4.7: a Copy Example 4.11 Continuing with Example 4.9: Sin- Example 4.13 Continuing with Example 4.11

The integration of EMST into the complete query-rewrite rule system enables us to eliminate the unnecessary complexity introduced by EMST in the query graph. EMST uses bcf adornments, can push equality and condition predicates, can push local and join predicates , adorns and transforms queries in one phase, can handle correlations, and is extensible. We have developed a cost-based heuristic to determine the join orders to be used for the magic-sets transformation, with the desirable property that the magic-sets transformation cannot degrade a query plan generated without doing the magic-sets transformation. Commercial database vendors are now realising the extreme importance of optimization for complex decision support queries. In some cases, much effort has been spent to optimize TPCD benchmark queries TPCD94] by hand in order to achieve better performance. The magic-sets transformation provides an opportunity to optimize decision support queries in a stable manner MFPR90a]. However, the relational vendors have found the magic-sets technique diicult to understand, and impractical to implement. We have explained magic-sets at an intuitive level, and have given suucient details of an actual implementation in a re-lational system to convince the vendors that (1) The perceived problems with implementing magic-sets have been solved by us, and (2) The magic-sets transformation can be implemented in relational database systems with modest eeort (about 6 person-months). Our implementation shows that it is feasible to build a magic-sets transformation module as a layer above existing relational databases, with feedback from the database systems about a join order to use for the magic-sets transformation, and with the guarantee that the resulting system will be as or more eecient than the existing system. Such an architecture brings the magic-sets transformation to the open world, and we hope it will encourage products from SQL conditioning companies. We also believe that our work will lead to many more implementations of the magic-sets transformation in commercial relational systems, especially for nonrecursive queries. FF93] J. Fessy and B. Finance. Early experience with recursion optimization in an extensible rewriter.sible/rule based query rewrite optimization in Star-burst. 5 Extensibility Extensibility is a major design goal in Starburst. A database customizer can add (1) features to the SQL language, leading to new operations in QGM boxes, (2) new optimization rules, (3) new traversal orders on the query graph, and (4) new access methods and cost-estimating functions, and so on. The query-rewrite optimizer, including the EMST rule, are required to be …