Granted Patent B2

US 9,773,032 · App. 13/251,190 · Granted Sep 26, 2017

Abstract

Claims (38)

1. A computer system comprising:

at least one processor; and

instructions recorded on a non-transitory computer-readable medium and executable by the at least one processor, the system including

a candidate index provider configured to cause the at least one processor to send a request to a cost estimator of a query optimizer of a database management system and receive, from the cost estimator and in response to the request, execution costs estimated by the cost estimator for execution against a database of a query workload having a plurality of queries, using at least one existing index, and based on estimation criteria determined by an execution analyzer of the query optimizer from analyzing the query workload as a whole, and further configured to cause the at least one processor to provide candidate indexes, based on the estimation criteria, wherein the candidate index provider is further configured to cause the at least one processor to transmit, to the cost estimator, a second request, to utilize the candidate indexes to re-estimate the execution costs to obtain updated execution costs for the query workload as a whole, using the candidate indexes; and

an index recommender configured to cause the at least one processor to receive the updated execution costs in response to the second request, and recommend a recommended index, based on the updated execution costs,

wherein the candidate index provider is configured to provide the candidate indexes including adding at least one query predicate selected from at least one query of the query workload for inclusion within the estimation criteria as a column of the at least one of the candidate indexes, and wherein the at least one query predicate includes at least two query predicates, and the candidate index provider is further configured to provide at least one of the candidate indexes including iteratively adding a query predicate of the at least two query predicates thereto, until a selectivity threshold is reached.

2. The system of claim 1 , wherein the execution analyzer is configured to analyze at least one access path for applying the plurality of queries against the database, to thereby provide the estimation criteria.

3. The system of claim 2 , wherein the execution analyzer is configured to extract predicates from the plurality of queries for inclusion within the estimation criteria.

4. 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.

5. 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.

6. 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.

7. The system of claim 1 , wherein the query workload is associated with at least one access path for applying queries of the query workload against the database.

8. The system of claim 7 , wherein the database management system comprises a workload comparator configured to compare execution costs of the at least one access path of the query workload with the updated execution costs of at least a second access path of a second query workload, the second access path using a second recommended index provided by the index recommender.

9. The system of claim 8 , 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.

10. The system of claim 1 , wherein the execution costs are calculated in terms of a number of seconds 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.

11. A computer-implemented method, comprising:

sending a request to a cost estimator of a query optimizer of a database management system for estimated execution costs for execution against a database of a query workload having a plurality of queries, using at least one existing index, and based on estimation criteria determined by an execution analyzer of the query optimizer from analyzing the query workload as a whole;

receiving, in response to the request, the execution costs calculated by the cost estimator for the plurality of queries of the query workload;

determining candidate indexes for the plurality of queries of the query workload,

based on the estimation criteria, including adding at least one query predicate selected from the query workload for inclusion within the estimation criteria as a column of the at least one of the candidate indexes;

transmitting a second request to the cost estimator to re-estimate the execution costs to obtain updated execution costs for the query workload as a whole, using the candidate indexes;

receiving the updated execution costs in response to the second request; and

recommending a recommended index, based on the updated execution costs, including 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,

wherein the at least one query predicate includes at least two query predicates, and wherein providing at least one of the candidate indexes includes iteratively adding a query predicate of the at least two query predicates thereto, until a selectivity threshold is reached.

12. The method of claim 11 , at least one of the candidate indexes is provided based on sort keys governing a sorted order of query results and included within the estimation criteria.

13. The method of claim 11 , wherein providing the candidate indexes comprises providing at least one of the candidate indexes including enhancing an existing index of the at least one existing index.

14. A computer program product, the computer program product being tangibly embodied on a non-transitory computer-readable medium and comprising instructions that, when executed, are configured to:

send a request to a cost estimator of a query optimizer of a database management system, estimated execution costs for execution against a database of a query workload having a plurality of queries, using at least one existing index, and based on estimation criteria determined by an execution analyzer of the query optimizer from analyzing the query workload as a whole;

receiving, in response to the request, the execution costs calculated by the cost estimator for the plurality of queries of the query workload;

determine candidate indexes for the plurality of queries of the query workload,

based on the estimation criteria, including adding at least one query predicate selected from the at least one query for inclusion within the estimation criteria as a column of the at least one of the candidate indexes;

transmit a second request to the cost estimator to re-estimate the execution costs to obtain updated execution costs for the query workload as a whole, using the candidate indexes;

receive the updated execution costs in response to the second request; and

recommend a recommended index, based on the updated execution costs, including 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,

wherein the at least one query predicate includes at least two query predicates, and wherein providing at least one of the candidate indexes includes iteratively adding a query predicate of the at least two query predicates thereto, until a selectivity threshold is reached.

15. The computer program product of claim 14 , wherein the execution analyzer is configured to analyze at least one access path for applying the query workload against the database, to thereby provide the estimation criteria.

16. The computer program product of claim 14 , 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.

17. The computer program product of claim 14 , 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.

Assignments (13)