Context-sensitive Indexes in RDBMS for Performance Optimization of SQL Queries in Multi-tenant/Multi-application Environments

Arjun K. Sirohi, Vidushi Sharma

2015

Abstract

With the recent shift towards cloud-based applications and Software as a Service (SaaS) environments, relational databases support multi-tenant and multi-application workloads that query the same set of data stored in common tables, using SQL queries. These SQL queries have very different query constructs and data-access requirements leading to different optimization needs. However, the business-users' expect sub-second response times in getting the data that they requested. The current RDBMS architectures where indexes “belong” to a table without any object privileges of their own, and, therefore, must be considered and used by the optimizer for all SQLs referencing the table(s), pose multiple challenges for the optimizer as well as application architects and performance tuning experts, especially as the number of such indexes grows. In this paper, we make the case for “Context-Sensitive Indexes”, whereby applications and tenants could define their own indexes on the shared, transactional database tables to optimize the execution of their SQL queries, while at the same time having the optimizer keep such indexes isolated from other applications and tenants/users for the purposes of query optimization.

References

  1. M. Leach, T. Lahdenmaki, 2005. Relational database index design and the optimizers: DB2, Oracle, SQL Server et al, John Wiley & Sons, Inc, Page: 4, 2005, ISBN-13 978-0-471-71999-1.
  2. Chong, R. F., 2012. Designing a database for multitenancy on the cloud - Considerations for SaaS vendors.
  3. https://www.ibm.com/developerworks/data/library/tec harticle/dm-1201dbdesigncloud.
  4. Chong F., Carraro G., and Wolter R., 2006. Multi-Tenant Data Architecture http://msdn.microsoft.com/enus/library/aa479086.aspx#mlttntda_topic2.
  5. Oracle White Paper - Application Development with Oracle Database 12c, http://www.oracle.com/ technetwork/database/multitenant/overview/index.htm l.
  6. K.S.K. Kihong, K. C. Sang, 2001. Optimizing Multidimensional Index Trees for Main Memory. In proceedings of the 2001 ACM SIGMOD international conference on Management of data.
  7. W. Powley, P. Martin, and P. Bird, 2008. Dbms workload control using throttling: experimental insights. In Proceedings of the conference of the center for advanced studies on collaborative research, pages 1{13, New York, NY, USA, 2008. ACM.
  8. Brown, K. P., Mehta, M., Carey, M. J., and Livny, M., 1994. Towards Automated Performance Tuning for Complex Workloads. In VLDB.
  9. Pang, H., Carey, M. J., and Livny, M., 1995. Multiclass Query Scheduling in Real-Time Database Systems. In IEEE Trans. on Knowl. And Data Eng.
  10. Krueger, J., Tinnefeld, C., Grund, M., Zeier, A., & Plattner, H., 2010. A case for online mixed workload processing. In DBTest.
  11. Oracle® Database SQL Language Reference 12c Release 1(12.1) http://docs.oracle.com/cd/E16655_01/server.121/e172 09/sql_elements008.htm#SQLRF51129.
  12. Oracle® Database Concepts 12c Release 1 (12.1). http://docs.oracle.com/cd/E16655_01/server.121/e176 33/indexiot.htm#CNCPT721.
  13. IBM DB2 10.1 for Linux, UNIX, and Windows documentation.
  14. http://pic.dhe.ibm.com/infocenter/db2luw/v10r1/index .jsp?topic=/com.
  15. Wasserman, Ted J., 2012. DB2 UDB security, Part 4: Understand how authorities and privileges are implemented in DB2 UDB. http://www.ibm.com/developerworks/data/library/tech article/dm-0601wasserman/
  16. Oracle® Database Performance Tuning Guide 11g Release 2 (11.2) Chapter 11 The Query Optimizer. https://docs.oracle.com/cd/E29597_01/server.1111/e1 6638/optimops.htm.
  17. Oracle® Database Security Guide 12c Release 1 (12.1). http://docs.oracle.com/cd/E16655_01/network.121/e17 607/authorization.htm#DBSEG99910.
  18. Oracle Database Online Documentation 11g Release 2 (11.2) / Database Administration, Database SQL Language Reference. https://docs.oracle.com/cd/ E11882_01/server.112/e41084/sql_elements006.htm# SQLRF51098.
  19. Oracle® Database Performance Tuning Guide 11g Release 2 (11.2) Chapter 19 Using Optimizer Hints. https://docs.oracle.com/cd/E11882_01/server.112/e41 573/hintsref.htm#PFGRF005.
  20. Oracle Database Online Documentation 12c Release 1 (12.1) / Database Administration Chapter 17 Introduction to the Multitenant Architecture. https://docs.oracle.com/database/121/CNCPT/cdbovrv w.htm#CNCPT89234.
Download


Paper Citation


in Harvard Style

K. Sirohi A. and Sharma V. (2015). Context-sensitive Indexes in RDBMS for Performance Optimization of SQL Queries in Multi-tenant/Multi-application Environments . In Proceedings of the 17th International Conference on Enterprise Information Systems - Volume 1: ICEIS, ISBN 978-989-758-096-3, pages 259-270. DOI: 10.5220/0005350802590270


in Bibtex Style

@conference{iceis15,
author={Arjun K. Sirohi and Vidushi Sharma},
title={Context-sensitive Indexes in RDBMS for Performance Optimization of SQL Queries in Multi-tenant/Multi-application Environments},
booktitle={Proceedings of the 17th International Conference on Enterprise Information Systems - Volume 1: ICEIS,},
year={2015},
pages={259-270},
publisher={SciTePress},
organization={INSTICC},
doi={10.5220/0005350802590270},
isbn={978-989-758-096-3},
}


in EndNote Style

TY - CONF
JO - Proceedings of the 17th International Conference on Enterprise Information Systems - Volume 1: ICEIS,
TI - Context-sensitive Indexes in RDBMS for Performance Optimization of SQL Queries in Multi-tenant/Multi-application Environments
SN - 978-989-758-096-3
AU - K. Sirohi A.
AU - Sharma V.
PY - 2015
SP - 259
EP - 270
DO - 10.5220/0005350802590270