Optimal plan search in a rule-based query optimizer

Abstract

This paper describes an optimal plan search strategy adopted in a rule-based query optimizer. Instead of attempting to search for the optimal plan directly, an initial plan is first generated based upon a set of heuristic rules. Depending upon the application, the initial plan may be used either as the final plan or as a base in a subsequent search. A new concept — clustering degree of an index — is introduced to better model the I/O costs of index scans. This new statistical information facilitates the formulation of the rules. An exhaustive search based upon the A* algorithm is then invoked to guarantee the optimal property of the plan. A lower bound value is derived and used as the estimation of ”remaining distance” required in the A* algorithm. Noteworthy features of our approach include the capability for dynamic control of exhaustive search for an optimal plan, and on-line performance monitoring/tuning. The preliminary results lead us to believe that the rule-based approach is a promising one to face the new challenges of the optimizer, as created by the requirements of supporting diversified applications.