# US 2013/0086038 A1 · Provision of index recommendations for database access

> Agents: this data is available as JSON from the patentgrep API and MCP server — see https://patentgrep.com/llms.txt

- Number: US 2013/0086038 A1
- Document ID: US20130086038A1
- Status: Published application
- Kind: A1
- Application number: 13/251,190
- Filed: 2011-09-30
- Published: 2013-04-04
- Earliest claimed: 2011-09-30
- Assignees: BMC Software, Inc.
- Inventors: Michael L. Perry
- Web page: https://patentgrep.com/patent/US20130086038A1

## CPC

- G06F 16/217: {Database tuning (G06F16/2282 takes precedence; database performance monitoring G06F11/3409)}
- G06F: ELECTRIC DIGITAL DATA PROCESSING (computer systems based on specific computational models G06N)

## Abstract

A cost estimator may estimate execution costs for execution of at least one query against a database, using at least one existing index, if any, and based on estimation criteria determined from analyzing the query execution. A candidate index provider may provide candidate indexes, based on the estimation criteria, and re-estimate the execution costs to obtain updated execution costs, using the candidate indexes. An index recommender may recommend a recommended index, based on the updated execution costs.

## Claims

### Claim 1

A computer system including instructions recorded on a computer-readable medium and executable by at least one processor, the system comprising:
a cost estimator configured to cause the at least one processor to estimate execution costs for execution of at least one query against a database, using at least one existing index, if any, and based on estimation criteria determined from analyzing the query execution;
a candidate index provider configured to cause the at least one processor to provide candidate indexes, based on the estimation criteria, wherein the cost estimator is further configured to re-estimate the execution costs to obtain updated execution costs, using the candidate indexes; and
an index recommender configured to cause the at least one processor to recommend a recommended index, based on the updated execution costs.

### Claim 2 (depends on 1)

The system of claim 1, comprising an execution analyzer configured to analyze an access path for applying the at least one query against the database, to thereby provide the estimation criteria.

### Claim 3 (depends on 2)

The system of claim 2, wherein the execution analyzer is configured to extract predicates from the at least one query for inclusion within the estimation criteria.

### Claim 4 (depends on 1)

The system of claim 1, wherein the candidate index provider is configured to provide at least one of the candidate indexes including enhancing an existing index of the at least one existing index.

### Claim 5 (depends on 1)

The system of claim 1, wherein the candidate index provider is configured to provide at least one of the candidate indexes including creating a new index based on the estimation criteria.

### Claim 6 (depends on 1)

The system of claim 1, wherein the candidate index provider is configured to provide at least one of the candidate indexes including adding at least one query predicate included within the estimation criteria as a column of the at least one of the candidate indexes.

### Claim 7 (depends on 1)

The system of claim 1, wherein the candidate index provider is configured to provide at least one of the candidate indexes based on sort keys governing a sorted order of query results and included within the estimation criteria.

### Claim 8 (depends on 1)

The system of claim 1, wherein the index report generator is configured to evaluate existing indexes and candidate indexes based on a selectivity thereof with respect to application of the at least one query in conjunction therewith against the database.

### Claim 9 (depends on 1)

The system of claim 1, wherein the at least one query is included within a workload of query statements, each statement including at least one query, the workload associated with at least one access path for applying queries of the workload against the database

### Claim 10 (depends on 8)

The system of claim 8, comprising a workload comparator configured to compare execution costs of the at least one access path of the workload with execution costs of at least a second access path of a second workload, the second access path using a second recommended index provided by the index recommender.

### Claim 11 (depends on 10)

The system of claim 10, wherein the workload and the second workload include execution counts enumerating a number of times that corresponding operations of the workloads are executed, and wherein the workload comparator is configured to weight the relative execution costs using the execution counts when comparing the access paths thereof.

### Claim 12 (depends on 1)

The system of claim 1, wherein the execution costs are calculated in terms of a number of second required to complete execution of the at least one query against the database, a number of processing cycles of the at least one processor required to complete execution of the at least one query against the database, and/or combinations thereof.

### Claim 13

A computer-implemented method, comprising:
estimating execution costs for execution of at least one query against a database, using at least one existing index, if any, and including storing estimation criteria determined from analyzing the query execution;
providing candidate indexes, based on the estimation criteria;
re-estimating the execution costs to obtain updated execution costs, using the candidate indexes; and
recommending a recommended index, based on the updated execution costs.

### Claim 14 (depends on 13)

The method of claim 13, wherein providing candidate indexes includes providing at least one of the candidate indexes including adding at least one query predicate included within the estimation criteria as a column of the at least one of the candidate indexes.

### Claim 15 (depends on 14)

The method of claim 14, wherein the recommending the recommended index includes evaluating existing indexes and candidate indexes based on a selectivity thereof with respect to application of the at least one query in conjunction therewith against the database.

### Claim 16

A computer program product, the computer program product being tangibly embodied on a computer-readable medium and comprising instructions that, when executed, are configured to:
estimate execution costs for execution of at least one query against a database, using at least one existing index, if any, and including storing estimation criteria determined from analyzing the query execution;
provide candidate indexes, based on the estimation criteria; re-estimate the execution costs to obtain updated execution costs, using the candidate indexes; and
recommend a recommended index, based on the updated execution costs.

### Claim 17 (depends on 16)

The computer program product of claim 16, comprising an execution analyzer configured to analyze an access path for applying the at least one query against the database, to thereby provide the estimation criteria.

### Claim 18 (depends on 16)

The computer program product of claim 16, wherein the instructions, when executed, are further configured to provide at least one of the candidate indexes including enhancing an existing index of the at least one existing index.

### Claim 19 (depends on 16)

The computer program product of claim 16, wherein the instructions, when executed, are further configured to provide at least one of the candidate indexes including creating a new index based on the estimation criteria.

### Claim 20 (depends on 16)

The computer program product of claim 16, wherein the instructions, when executed, are further configured to provide at least one of the candidate indexes including adding at least one query predicate included within the estimation criteria as a column of the at least one of the candidate indexes.
