Optimal plan search in a rule-based query optimizer

  • Ming-Chien Shan
Expert System Approaches To Databases
Part of the Lecture Notes in Computer Science book series (LNCS, volume 303)


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.


Exhaustive Search Query Processing Relational Algebra Optimal Plan Database Management System 
These keywords were added by machine and not by the authors. This process is experimental and the keywords may be updated as the learning algorithm improves.


Unable to display preview. Download preview PDF.

Unable to display preview. Download preview PDF.

7 References

  1. 1.
    Astrahan, M., Schkolnick, M., and Whang, K., ”Counting unique values of an attribute without sorting”, IBM Research Report RJ4960, December, 1985.Google Scholar
  2. 2.
    Batory, D., et al., ”GENESIS: A Reconfigurable Database Management System”, Technical Report 86-07, Dept. of Computer Science, The University of Texas, Austin, 1986.Google Scholar
  3. 3.
    Beech, D., ”A Foundation for Evolution from Relational to Object Databases”, Proc. of Int. Conf. on EDBT, Venice, Italy, March, 1988.Google Scholar
  4. 4.
    Bernstein, P., et al., ”Query Processing in a System for Distributed Databases(SDD-1)” ACM Trans. on Database Systems, Vol.6, No. 4, December, 1981.Google Scholar
  5. 5.
    Bitton, D., DeWitt, D., and Turbyfill C., ”Benchmarking database systems — A systematic approach”, Proc. of 9th Int. Conf. on VLDB, 1983.Google Scholar
  6. 6.
    Bloom, B., ”Space/Time Trade-offs in Hash Coding with Allowable Errors”, Comm. ACM, Vol. 13, No. 7, July, 1970.Google Scholar
  7. 7.
    Bratbergsengen, K., ”Hashing methods and relational algebra operations”, Proc. of 10th Int. Conf. on VLDB, August, 1984.Google Scholar
  8. 8.
    Carey, M., and Lu, H., ”Some Experimental Results on Distributed Join Algorithms in a Local Network”, Proc. of 11th Int. Conf. on VLDB, Sweden, August, 1985.Google Scholar
  9. 9.
    Christodoulakis, S., ”Estimating Block Transfers and Join Sizes”, Proc. ACM-SIGMOD, May, 1983.Google Scholar
  10. 10.
    Date, C., ”A guide to the SQL standard”, Addison-Wesley Publishing Company, 1987.Google Scholar
  11. 11.
    Dayal, U., and Smith, J., ”PROBE: A Knowledge Oriented Database Management System”, Proc. of the Islamorada Workshop on large scale knowledge base and reasoning systems, February, 1985.Google Scholar
  12. 12.
    Derrett, N., et al, ”An object-oriented approach to data management”, Proc. Compcon 31th IEEE Computer Society Int. Conf., San Francisco, March, 1986.Google Scholar
  13. 13.
    Derrett, N. and Shan, M., ”Rule-based Query Optimization”, Hewlett-Packard Lab. Research Report STL-87-07, December, 1987.Google Scholar
  14. 14.
    Fishman, D., et al., ”Iris: An Object-Oriented Database Management System”, ACM Trans. on Office Information Systems, Vol. 5, No. 1, January, 1987.Google Scholar
  15. 15.
    Freytag, J., ”A Rule-based View of Query Optimization”, Proc. ACM-SIGMOD, San Francisco, CA., May, 1987.Google Scholar
  16. 16.
    Graefe, G., and Dewitt, D., ”The EXODUS Optimizer Generator”, Proc. ACM-SIGMOD, San Francisco, CA., May, 1987.Google Scholar
  17. 17.
    Hagmann, R., ”An Observation on Database Buffering Performance Metrics”, Proc. of 12th Int. Conf. on VLDB, Tokyo, August, 1986.Google Scholar
  18. 18.
    Ioannidis, Y., and Wong, E., ”Query Optimization by Simulated Annealing”, Proc. ACM-SIGMOD, San Francisco, CA., May, 1987.Google Scholar
  19. 19.
    Jarke, M., and Koch, J., ”Query Optimization in Database Systems”, ACM Computing Surveys, Vol. 16, No. 2, June, 1984.Google Scholar
  20. 20.
    Kitsuregawa, M., et al., ”Application of Hash to Data Base Machine and its architecture”, New Generation Computing, No.1, 1983.Google Scholar
  21. 21.
    Mackert, L., and Lohman, G., ”Index scan using a finite LRU buffer: A validated I/O model”, IBM Research Report RJ4836, September, 1985.Google Scholar
  22. 22.
    Mackert, L., and Lohman, G., ”R* Optimizer validation and Performance Evaluation for Local Queries”, Proc. ACM-SIGMOD, Washington, D.C., May, 1986.Google Scholar
  23. 23.
    Piatetsky-Shapiro, G., and Connell, C., ”Accurate estimation of the number of tuples satisfying a condition”, Proc. ACM-SIGMOD, May, 1984.Google Scholar
  24. 24.
    Rosenthal, A., ”Note on the expected size of a join”, SIGMOD record, July, 1981.Google Scholar
  25. 25.
    Rosenthal, A., Dayal, U., and Reiner, D., ”Fast Query Optimization over a Large Strategy Space: The Pilot Pass Approach”, Unpublished manuscript, 1986.Google Scholar
  26. 26.
    Ryan, T., and Cate, H., ”Performance evaluations of Iris/OSQL and HP-SQL”, Unpublished manuscript, December, 1987.Google Scholar
  27. 27.
    Schwarz, P., et al., ”Extensibility in the Starburst Database System”, Int. Workshop on Object-Oriented Database Systems, Pacific Grove, CA., September, 1986.Google Scholar
  28. 28.
    Selinger, P. et al., ”Access path selection in a relational database management system”, Proc. ACM-SIGMOD, Boston, MA., June, 1979.Google Scholar
  29. 29.
    Shan, M., ”Rule management in Database Systems”, Proc. 6th Advanced Database Symposium, Tokyo, Japan, August, 1986.Google Scholar
  30. 30.
    Shan, M., ”Rule-based Query Optimization in PC environment — A heuristic approach”, Hewlett-Packard Lab. Research Report, In preparation.Google Scholar
  31. 31.
    Stonebraker, M., and Rowe, L., ”The Design of POSTGRES”, Proc. ACM-SIGMOD, Washington, D.C., May, 1986.Google Scholar
  32. 32.
    Winston, P., ”Artificial Intelligence”, 2nd Edition, Addison-Wesley Publishing Company, 1984.Google Scholar
  33. 33.
    Wong, E., and Youssefi, K., ”Decomposition: A Strategy for Query Processing”, Proc. ACM Trans. on Database Systems, Vol. 1, No. 3, September, 1976.Google Scholar
  34. 34.
    Yu, C., and Chang, C., ”Distributed Query Processing”, ACM Computing Surveys, Vol. 16, No. 4, December, 1984.Google Scholar

Copyright information

© Springer-Verlag Berlin Heidelberg 1988

Authors and Affiliations

  • Ming-Chien Shan
    • 1
  1. 1.Hewlett-Packard LaboratoriesPalo Alto

Personalised recommendations