Query Optimization in Deductive and Relational Databases

Optimization is critical to the success of declarative database systems. We develop a powerful extended magic-sets transformation (EMST) for optimization of complex queries in relational and deductive database systems. EMST works by rewriting database queries so that predicates are applied as early as possible (predicate push down) during a bottom-up evaluation of the rewritten query. The magic-sets transformation has earlier been proposed as an optimization technique for recursive queries in deductive databases. We strengthen the technique into an invaluable optimization for all types of queries in practical database systems. (1) The magic-sets transformation can only use equality predicates to restrict computation. We develop the ground magic-sets transformation to push down arbitrary built-in predicates, such as Salary $>$ 70K. (2) The magic-sets transformation is not applicable in presence of duplicates and aggregates supported by practical query languages such as SQL. We define formal semantics for a language with duplicates, aggregates, and recursion, and define EMST for such a language. (3) We demonstrate the importance of EMST on nonrecursive queries through performance experiments on IBM's DB2 database system. We compare EMST with correlation, a traditional SQL optimization technique for pushing down predicates in nonrecursive queries. The conclusion is that EMST is a stable transformation and should replace correlation. A subset of the extended magic-sets transformation has been implemented in the Starburst extensible database system being developed at the IBM Almaden Research Center. The extended magic-sets transformation furthers the state of the art in query optimization for deductive and relational databases.