Alternative Plan Generation methods for Multiple Query Optimization

Alternative Plan Generation (APG) is an important phase of Multiple Query Optimization (MQO) in relational databases. It is necessary to generate a number of alternative plans in such a way that the sharing between queries is maximized and an optimal execution plan with minimal cost is obtained. For relational databases several methods have previously been proposed for generating alternative plans using commutativity and associativity properties of select, project and join operations. When all possible alternative plans are generated using these properties, the number of alternative plans to be used in MQO will be quite large leading to an unacceptable increase in the cost of APG which eliminates the benefits of MQO for query execution. The quality of the alternative plans determines the cost of the global execution plan for the queries. In this paper, we propose a new method for APG that uses information about the queries to best utilize the sharing between the queries. This method generates the alternative plans for queries having more common tasks by introducing the factors that provides a good estimation of shared tasks of queries using information such as common relations, common possible joins and common conditions. We also compare benefits obtained from MQO with the previously proposed APG methods and with our method, and show that it is possible to find a near optimal solution with this technique. For 14 queries and a database of 15 relations on the average, MQO performs 30 times faster by using the alternative plans generated by this new method while we are within 7% of the optimal solution.