Authors:
Arjun K. Sirohi
1
and
Vidushi Sharma
2
Affiliations:
1
Oracle U.S.A. Inc, United States
;
2
Gautam Buddha University, India
Keyword(s):
Indexes, Mixed-load Queries, Multi-tenant Queries, Multi-application Queries, SQL Performance Optimization.
Related
Ontology
Subjects/Areas/Topics:
Data Engineering
;
Databases and Data Security
;
Databases and Information Systems Integration
;
Distributed Database Systems
;
Enterprise Information Systems
;
Large Scale Databases
;
Query Languages and Query Processing
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, transac
tional 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.
(More)