Abstract
Query optimization is a challenging task for database management researchers. After parsing of queries during query processing, in query optimization step, various query execution plans are generated. The job of query optimizer is to propose an optimal plan that can evaluate the given relational expression at a reasonably lower cost. For every new input query instance, generating multiple execution plans and identifying an efficient optimal plan amongst is always challenging in terms of consumption of resources and costs associated with optimization. As the number of plans increases, it can take longer to find a good plan. Thus, to make query optimization practical and efficient, reusing the existing execution plans will provide the ideal solution for the new instances of equivalent old queries.
In the paper, a novel design approach for execution of parametric query has been proposed, where query may have generic (reusable) and specific parameters. The heuristic transformations and query tree representations help to find the best plan among of all possible plans. This best plan is compared with plans stored in plan cache to find a equivalent generic plan. Feature extraction and similarity detection techniques are used to compare cached plan for reuse. If no generic plan is found, then by using dynamic programming heuristic search algorithm, cost of plan is computed before optimization and execution of query instance.
This is a preview of subscription content, log in via an institution to check access.
Access this chapter
Tax calculation will be finalised at checkout
Purchases are for personal use only
Similar content being viewed by others
References
Deepak, S.: The performance enhancement approach for parameterized queries. In: CSI Sixth International Conference on Software Engineering (CONSEG), Indore (2012)
Ghazal, A., Seid, D., Ramesh, B., Crolotte, A., Koppuravuri, M., Vinod, G.: Dynamic plan generation for parameterized queries. In: Proceedings of the 2009 ACM SIGMOD International Conference on Management of data, Providence, Rhode Island, USA (2009).https://doi.org/10.1145/1559845.1559946
Ganguly, S.: Design and analysis of parametric query optimization algorithms. In: Proceedings of the 24th VLDB Conference, New York, USA (1998)
Dutt, A., Narasayya, V., Chaudhuri, S.: Leveraging re-costing for online optimization of parameterized queries. In: Proceedings of the 2017 ACM International Conference on Management of Data, Chicago, Illinois, USA. https://doi.org/10.1145/3035918.3064040(2017)
Ioannidis, Y.E., Ng, R.T., Shim, K., Sellis, T.K.: Parametric query optimization. In: Proceedings of the 18th International Conference on Very Large Data Bases (VLDB). Morgan Kaufmann Publishers Inc. San Francisco (1992). https://doi.org/10.1007/s007780050037
Singh, V.: Multi-objective parametric query optimization for distributed database systems. In: Pant, M., Deep, K., Bansal, J., Nagar, A., Das, K. (eds) Proceedings of Fifth International Conference on Soft Computing for Problem Solving, vol. 436, pp. 219-234. Springer, Singapore (2016). https://doi.org/10.1007/978-981-10-0448-3_18
Zahir, J., Qadi, A.E.: A recommendation system for execution plans using machine learning. Math. Comput. Appl. 21, 23 (2016). https://doi.org/10.3390/mca21020023
Zahir, J., Qadi, A.E., Aboutajdine, D.: Access plan recommendation using SQL queries similarity. WSEAS Trans. Comput. 14, 638–645 (2015). https://doi.org/10.37394/23205.2020.19
Trummer, I., Koch, C.: Multi-objective parametric query optimization. VLDB J. 26, 107–124 (2017). https://doi.org/10.1007/s00778-016-0439-0
Hulgeri, A., Sudarshan, S.: Parametric query optimization for linear and piecewise linear cost functions. In: Proceedings of the 28th VLDB Conference, Hong Kong, China (2002). https://doi.org/10.1016/b978-155860869-6/50023-8
Trummer, I., Koch, C.: An incremental anytime algorithm for multi-objective query optimization. In: Proceedings of the 2015 ACM SIGMOD International Conference on Management of Data Melbourne, Victoria, Australia (2015). https://doi.org/10.1145/2723372.2746484
Ramachandra, K., Sudarshan, S.: Holistic optimization by prefetching query results. In: Proceedings of the 2012 ACM SIGMOD International Conference on Management of Data, Scottsdale, Arizona, USA (2012). https://doi.org/10.1145/2213836.2213852
Wu, W., Chi, Y., Zhu, S., Tatemura, J.: Predicting query execution time: are optimizer cost models really unusable? In: Proceedings of the 2013 IEEE International Conference on Data Engineering, IEEE Computer Society Washington, DC, USA (2013). https://doi.org/10.1109/icde.2013.6544899
Sinaga, A.M., Sibarani, P.: Implementation of caching database to reduce query’s response time. In: MATEC Web of Conferences, The 3rd Bali International Seminar on Science & Technology, Bali, Indonesia, vol. 58 (2016). https://doi.org/10.1051/matecconf/20165803014
Kifer, M., Bernstein, A., Lewis, P., Panigrahi, P.K.: Database Systems: An Application Oriented Approach. Introductory Version, Second Edition. Pearson Education (2008)
Author information
Authors and Affiliations
Corresponding author
Editor information
Editors and Affiliations
Rights and permissions
Copyright information
© 2021 Springer Nature Singapore Pte Ltd.
About this paper
Cite this paper
Gawali, R.D., Shinde, S.K. (2021). Novel Design Approach for Optimal Execution Plan and Strategy for Query Execution. In: Garg, D., Wong, K., Sarangapani, J., Gupta, S.K. (eds) Advanced Computing. IACC 2020. Communications in Computer and Information Science, vol 1368. Springer, Singapore. https://doi.org/10.1007/978-981-16-0404-1_23
Download citation
DOI: https://doi.org/10.1007/978-981-16-0404-1_23
Published:
Publisher Name: Springer, Singapore
Print ISBN: 978-981-16-0403-4
Online ISBN: 978-981-16-0404-1
eBook Packages: Computer ScienceComputer Science (R0)