A Novel Paging Query Optimization Technique for Relational Databases
Database systems are designed to store data in structured formfor efficient retrieval and processing. SQL fetches data from these databases in the form of pages each consisting of several rows. Paging query has direct impact on the systemperformance. The major bottleneck for large databases that affect paging query is the volume of data. Since a full table scan is involved in this process so a significant transfer time is required to move data between storage media and database server. We propose an optimized solution for paging query based on key technologies of paging query efficiency. The proposed solution for SQL optimization is based on the concept of offloading. Contrary to the typicaldatabase systems, only the data sought by the client is returned to the database instance from storage. ShiftingSQL processing off the database server eliminates massive amount of futileI/O transfers. Hence, queries run much faster and are processed in a shorter timespan. The experimental results show that the proposed solution is effective and efficient.
How to Cite
Muhammad Naeem Ahmed Khan. (2019). A Novel Paging Query Optimization Technique for Relational Databases. International Journal of Next-Generation Computing, 10(1), 56–65. https://doi.org/10.47164/ijngc.v10i1.155
- A., H. and F., M. 2009. Evolution of Query Optimization Methods. In Transactions on LargeScale Data- and Knowledge-Centered Systems I, H. A., K. J., and W. R., Eds. Springer Berlin Heidelberg, Berlin, Heidelberg, 211–242.
- Ali Khan, Z. and Khan, N. 2018. Data modeling and query optimization technique in business intelligence applications. International Journal of Advanced Science and Technology 114, 139–150.
- Bach, M., Arao, K., Colvin, A., Hoogland, F., Osborne, K., Johnson, R., and Poder, T. 2015. Expert Oracle Exadata.
- Bellamkonda, S., Ahmed, R., Witkowski, A., Amor, A., Zait, M., and Lin, C.-C. 2009.Enhanced subquery optimizations in oracle. Proceedings of the VLDB Endowment 2, 2, 1366–1377.
- Bruno, N., Chaudhuri, S., and Ramamurthy, R. 2009. Power hints for query optimization. In 2009 IEEE 25th International Conference on Data Engineering. 469–480.
- Chaudhuri, S. 1998. An overview of query optimization in relational systems. In Proceedings of the Seventeenth ACM SIGACT-SIGMOD-SIGART Symposium on Principles of Database Systems. PODS ’98. ACM, New York, NY, USA, 34–43.
- Eidson, W. C. and Collins, J. 2016. Methods and systems for joining indexes for query optimization in a multi-tenant database. US Patent 9,405,797.
- Gupta, M. and Chandra, P. 2011. An empirical evaluation of like operator in oracle. BVICAM’s International Journal of Information Technology 3.
- Herodotou, H., Borisov, N., and Babu, S. 2011. Query optimization techniques for partitioned tables. In Proceedings of the 2011 ACM SIGMOD International Conference on Management of Data. SIGMOD ’11. ACM, New York, NY, USA, 49–60.
- Ioannidis, Y. E. 1996. Query optimization. ACM Comput. Surv. 28, 1 (Mar.), 121–123.
- Khan, M. and Khan, M. 2013. Exploring query optimization techniques in relational databases.International Journal of Database Theory and Application (IJDTA) 6, 11–20.
- Li, D., Han, L., and Ding, Y. 2010. Sql query optimization methods of relational database system. Computer Engineering and Applications, International Conference on 1, 557–560.
- Lohman, G. 2014. Is query optimization a solved problem. In Proc. Workshop on Database Query Optimization. Oregon Graduate Center Comp. Sci. Tech. Rep, 13.
- O’Neil, P. and Graefe, G. 1995. Multi-table joins through bitmapped join indices. ACMSIGMOD Record 24, 3, 8–11.
- Sun, F. and Wang, L. 2011. Paging query optimization of massive data in oracle 10g database. In 2011 International Conference on Computer Science and Service System (CSSS). 2388–2391.
- Sun, P., Zhao, Z., and Ge, Z. 2009. The research on the query optimization strategy of the vdsi system database. In 2009 Asia-Pacific Conference on Computational Intelligence and Industrial Applications (PACIIA). Vol. 1. 79–82.
- Zafarani, E., Feizi Derakhshi, M., Asil, H., and Asil, A. 2010. Presenting a new method for optimizing join queries processing in heterogeneous distributed databases. In 2010 Third International Conference on Knowledge Discovery and Data Mining. 379–382.