Index Tuning through Query Evaluation Mechanism Based on Indirect Domain Knowledge

Abstract

Query prioritization for index tuning leads to improvement of performance in databases. We propose an analytical model which centers around managing index objects that are aligned to the queries submitted by the users through an Online Transaction Processing (OLTP) system. We first define a strategy to prioritize queries based on certain configurable parameters and call them as short listed queries. The next step is to analyze the existing index objects that were created by the DBA during the initial database setup and to check if these index objects are aligned to the short listed queries. Eventually, the system generates a recommendation report to the DBA which lists new indexes to be created that are aligned to short listed queries and list of obsolete indexes that are potential candidates to be dropped due to low utilization rates. This approach introduces a new facet for adoption towards performance analysis and improvements in software systems. We present an experimental analysis that validates the ideas of feeding domain knowledge indirectly in to the system through configurable parameters that help in prioritizing queries for Index tuning.