Browse Prior Art Database

Index Competition Based on Column Coverage Analysis and Comparison Disclosure Number: IPCOM000245380D
Publication Date: 2016-Mar-04
Document File: 3 page(s) / 73K

Publishing Venue

The Prior Art Database


Disclosed is an apparatus, system, or method to compete candidate indexes based on column coverage at an early stage in a relational database management system (RDBMS).

This text was extracted from a PDF file.
This is the abbreviated version, containing approximately 41% of the total text.

Page 01 of 3

Index Competition Based on Column Coverage Analysis and Comparison

With the rapid growth on database applications, today's application development models may result in many application-generated complex queries and database designs with dozens, even hundreds, of candidate indexes on a single table.

Enterprise Resource Planning (ERP) applications often generate indexes to support all of the functionality of the application. A customer may only exploit a fraction of such

functionality. Alternatively, it is likely that each query against the underlying relational database management system (RDBMS) is only interested in a subset of the indexes defined. This type of design is becoming more common in applications that focus on rapid deployment rather than focus on the performance in a mission critical or high volume environment.

In an RDBMS, the cost-based optimizer evaluates every viable join sequence, join type, and access method to determine the lowest estimated cost access plan. In order to get the most optimal access plan, the cost-based optimizer has to evaluate each candidate index in each join context to determine the lowest cost. The large number of application- generated indexes and complex queries can increase the time and space required by a traditional cost based optimizer in access plan selection, and thus a large number of indexes becomes a burden on both performance and accuracy of access plan selection.

The novel contribution is an apparatus, system, or method to compete candidate indexes based on column coverage at an early stage. This can significantly improve the performance of the access path selection process and improve the performance of the chosen access plan. This solution becomes critical if RDBMSs designed for mission critical systems are to adapt to market change of rapid application deployment

with minimal focus on explicit tuning for performance.

Most of the time, indexes satisfy a certain purpose (e.g., satisfy a certain order, provide filtering to reduce data access cost, provide index only access for improved performance by avoiding access to the underlying data pages, etc.). The large variation in functionality of an application-generated query workload might result in a large number of indexes with different sets of columns and column sequences to improve the performance of the entire query workload; however, it is likely that only a small subset
of the indexes might be helpful to an individual query.

Considering the different purposes on index creation, poor index candidates can be eliminated by evaluating the column coverage of the query compared to the indexes, the interesting order of columns that may support order by, group by, distinct or joins, and identify indexes that are a subset/superset of each other. This evaluation can be done at early stage, in a context-free manner, before costing the candidate indexes in detail through the potential join sequences. Eliminating unnecessary candidate in...