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.
This is a preview of subscription content, log in via an institution to check access.
Preview
Unable to display preview. Download preview PDF.
7 References
Astrahan, M., Schkolnick, M., and Whang, K., ”Counting unique values of an attribute without sorting”, IBM Research Report RJ4960, December, 1985.
Batory, D., et al., ”GENESIS: A Reconfigurable Database Management System”, Technical Report 86-07, Dept. of Computer Science, The University of Texas, Austin, 1986.
Beech, D., ”A Foundation for Evolution from Relational to Object Databases”, Proc. of Int. Conf. on EDBT, Venice, Italy, March, 1988.
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.
Bitton, D., DeWitt, D., and Turbyfill C., ”Benchmarking database systems — A systematic approach”, Proc. of 9th Int. Conf. on VLDB, 1983.
Bloom, B., ”Space/Time Trade-offs in Hash Coding with Allowable Errors”, Comm. ACM, Vol. 13, No. 7, July, 1970.
Bratbergsengen, K., ”Hashing methods and relational algebra operations”, Proc. of 10th Int. Conf. on VLDB, August, 1984.
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.
Christodoulakis, S., ”Estimating Block Transfers and Join Sizes”, Proc. ACM-SIGMOD, May, 1983.
Date, C., ”A guide to the SQL standard”, Addison-Wesley Publishing Company, 1987.
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.
Derrett, N., et al, ”An object-oriented approach to data management”, Proc. Compcon 31th IEEE Computer Society Int. Conf., San Francisco, March, 1986.
Derrett, N. and Shan, M., ”Rule-based Query Optimization”, Hewlett-Packard Lab. Research Report STL-87-07, December, 1987.
Fishman, D., et al., ”Iris: An Object-Oriented Database Management System”, ACM Trans. on Office Information Systems, Vol. 5, No. 1, January, 1987.
Freytag, J., ”A Rule-based View of Query Optimization”, Proc. ACM-SIGMOD, San Francisco, CA., May, 1987.
Graefe, G., and Dewitt, D., ”The EXODUS Optimizer Generator”, Proc. ACM-SIGMOD, San Francisco, CA., May, 1987.
Hagmann, R., ”An Observation on Database Buffering Performance Metrics”, Proc. of 12th Int. Conf. on VLDB, Tokyo, August, 1986.
Ioannidis, Y., and Wong, E., ”Query Optimization by Simulated Annealing”, Proc. ACM-SIGMOD, San Francisco, CA., May, 1987.
Jarke, M., and Koch, J., ”Query Optimization in Database Systems”, ACM Computing Surveys, Vol. 16, No. 2, June, 1984.
Kitsuregawa, M., et al., ”Application of Hash to Data Base Machine and its architecture”, New Generation Computing, No.1, 1983.
Mackert, L., and Lohman, G., ”Index scan using a finite LRU buffer: A validated I/O model”, IBM Research Report RJ4836, September, 1985.
Mackert, L., and Lohman, G., ”R* Optimizer validation and Performance Evaluation for Local Queries”, Proc. ACM-SIGMOD, Washington, D.C., May, 1986.
Piatetsky-Shapiro, G., and Connell, C., ”Accurate estimation of the number of tuples satisfying a condition”, Proc. ACM-SIGMOD, May, 1984.
Rosenthal, A., ”Note on the expected size of a join”, SIGMOD record, July, 1981.
Rosenthal, A., Dayal, U., and Reiner, D., ”Fast Query Optimization over a Large Strategy Space: The Pilot Pass Approach”, Unpublished manuscript, 1986.
Ryan, T., and Cate, H., ”Performance evaluations of Iris/OSQL and HP-SQL”, Unpublished manuscript, December, 1987.
Schwarz, P., et al., ”Extensibility in the Starburst Database System”, Int. Workshop on Object-Oriented Database Systems, Pacific Grove, CA., September, 1986.
Selinger, P. et al., ”Access path selection in a relational database management system”, Proc. ACM-SIGMOD, Boston, MA., June, 1979.
Shan, M., ”Rule management in Database Systems”, Proc. 6th Advanced Database Symposium, Tokyo, Japan, August, 1986.
Shan, M., ”Rule-based Query Optimization in PC environment — A heuristic approach”, Hewlett-Packard Lab. Research Report, In preparation.
Stonebraker, M., and Rowe, L., ”The Design of POSTGRES”, Proc. ACM-SIGMOD, Washington, D.C., May, 1986.
Winston, P., ”Artificial Intelligence”, 2nd Edition, Addison-Wesley Publishing Company, 1984.
Wong, E., and Youssefi, K., ”Decomposition: A Strategy for Query Processing”, Proc. ACM Trans. on Database Systems, Vol. 1, No. 3, September, 1976.
Yu, C., and Chang, C., ”Distributed Query Processing”, ACM Computing Surveys, Vol. 16, No. 4, December, 1984.
Author information
Authors and Affiliations
Editor information
Rights and permissions
Copyright information
© 1988 Springer-Verlag Berlin Heidelberg
About this paper
Cite this paper
Shan, MC. (1988). Optimal plan search in a rule-based query optimizer. In: Schmidt, J.W., Ceri, S., Missikoff, M. (eds) Advances in Database Technology—EDBT '88. EDBT 1988. Lecture Notes in Computer Science, vol 303. Springer, Berlin, Heidelberg. https://doi.org/10.1007/3-540-19074-0_49
Download citation
DOI: https://doi.org/10.1007/3-540-19074-0_49
Published:
Publisher Name: Springer, Berlin, Heidelberg
Print ISBN: 978-3-540-19074-5
Online ISBN: 978-3-540-39095-4
eBook Packages: Springer Book Archive