Optimization of queries using retrieval status of resources used thereby

• US 7,308,437 B2
• Filed: 10/22/2003
• Issued: 12/11/2007
• Est. Priority Date: 10/22/2003
• Status: Active Grant

• Current Assignee: Google LLC (Alphabet Inc.)
• Original Assignee: International Business Machines Corporation
• Inventors: Day, Paul Reuben, Muras, Brian Robert
• Primary Examiner(s): Pham; Khanh B.
• Application Number:
  US10/691,296
• Publication Number:
  US 20050091196A1
• Time in Patent Office: 1,511 Days
• Field of Search: 707 1- 10, 707/203, 709/203
• US Class Current: 1/1
• CPC Class Codes: G06F 16/24542 Plan optimisation
• CPC Class Codes: Y10S 707/99932 Access augmentation or optimizing
• CPC Class Codes: Y10S 707/99933 Query processing, i.e. searching
• CPC Class Codes: Y10S 707/99934 Query formulation, input preparation, or translation
• CPC Class Codes: Y10S 707/99935 Query augmenting and refining, e.g. inexact access

Abstract

An apparatus, program product and method utilize the retrieval status of a resource to generate an access plan for a database query that uses the resource. The retrieval status, which indicates, for example, what percentage of a given resource is already stored in working memory, and thus need not be separately retrieved during query execution, may be used to generate more accurate input/output cost estimates for access plans, and thus enable a query optimizer to better select an access plan that is optimal under given runtime conditions.

Claims

1. A method of optimizing a database query, the method comprising:

determining a retrieval status for a resource used by the database query, wherein determining the retrieval status includes determining a percentage of the resource that is resident in a working memory;

generating an access plan for the database query using the determined retrieval status for the resource, wherein generating the access plan includes;

generating a plurality of alternate access plans;

calculating a cost for each alternate access plan using the determined retrieval status for the resource; and

selecting one of the alternate access plans based upon the calculated costs for each alternate access plan;

storing the access plan, including associating with the stored access plan a retrieval status assumption for the stored access plan, the retrieval status assumption representing the determined retrieval status used to generate the access plan; and

in response to a request to execute the stored access plan;

determining a current retrieval status for the resource, wherein determining the current retrieval status includes determining a percentage of the resource that is currently resident in the working memory;

comparing the current retrieval status with the retrieval status assumption; and

selectively generating another access plan for the database query using the current retrieval status based upon the comparison of the current retrieval status with the retrieval status assumption.

2. The method of claim 1, wherein the resource is selected from the group consisting of a database file, a database table, an index, a temporary result set, a temporary file, a hash table, and combinations thereof.

3. The method of claim 1, wherein determining the retrieval status includes determining whether at least a portion of the resource is resident in working memory.

4. The method of claim 1, wherein determining the retrieval status includes determining whether at least a portion of the resource is resident in a cache memory.

5. The method of claim 1, wherein determining the retrieval status includes determining whether at least a portion of the resource is resident in a local or a remote memory.

6. The method of claim 1, wherein determining the retrieval status includes accessing a resource manager to obtain the retrieval status of the resource.

7. The method of claim 6, further comprising tracking, with the resource manager, the percentage of the resource that is in the working memory.

8. The method of claim 7, further comprising storing the percentage of the resource that is in working memory in a header for a persistent copy of the resource.

9. The method of claim 1, and wherein calculating the cost for each alternate access plan includes:

calculating the cost as a function of input/output cost and processing cost; and

calculating the input/output cost by scaling an estimated input/output cost by a scalar value associated with the percentage of the resource that is resident in working memory.

10. The method of claim 1, wherein determining the retrieval status includes determining whether a beginning portion of the resource is resident in working memory, the method further comprising calculating a cost for the access plan based upon the determined retrieval status, including weighting the cost based upon the beginning portion of the resource being resident in working memory.

11. An apparatus, comprising:

at least one processor;

a memory that includes a working memory;

a resource manager; and

program code resident in the memory and configured to be executed by the at least one processor to optimize a database query by determining a retrieval status for a resource used by the database query, and generating an access plan for the database query using the determined retrieval status for the resource, wherein the program code is configured to determine the retrieval status by accessing the resource manager to obtain the retrieval status of the resource, wherein the resource manager is configured to track a percentage of the resource that is in the working memory, and wherein the resource manager is further configured to store the percentage of the resource that is in working memory in a header of a persistent copy of the resource.

12. The apparatus of claim 11, wherein the resource is selected from the group consisting of a database file, a database table, an index, a temporary result set, a temporary file, a hash table, and combinations thereof.

13. The apparatus of claim 11, wherein the program code is configured to determine the retrieval status by determining whether at least a portion of the resource is resident in the working memory.

14. The apparatus of claim 11, wherein the memory further includes a cache memory, and wherein the program code is configured to determine the retrieval status by determining whether at least a portion of the resource is resident in the cache memory.

15. The apparatus of claim 11, wherein the working memory includes a local memory, wherein the memory further includes a remote memory, and wherein the program code is configured to determine the retrieval status by determining whether at least a portion of the resource is resident in the local or the remote memory.

16. The apparatus of claim 11, wherein the program code is configured to determine the retrieval status by determining a percentage of the resource that is resident in the working memory.

17. The apparatus of claim 11, wherein the program code is configured to generate the access plan by generating a plurality of alternate access plans, calculating a cost for each alternate access plan using the determined retrieval status for the resource, and selecting one of the alternate access plans based upon the calculated costs for each alternate access plan.

18. The apparatus of claim 17, wherein the retrieval status for the resource indicates a percentage of the resource that is resident in the working memory, and wherein the program code is configured to calculate the cost for each alternate access plan by calculating the cost as a function of input/output cost and processing cost, and calculating the input/output cost by scaling an estimated input/output cost by a scalar value associated with the percentage of the resource that is resident in the working memory.

19. The apparatus of claim 17, wherein the program code is further configured to store the access plan and associate with the stored access plan a retrieval status assumption for the stored access plan, wherein the retrieval status assumption represents the determined retrieval status used to generate the access plan, and wherein the program code is further configured to, in response to a request to execute the stored access plan, determine a current retrieval status for the resource, compare the current retrieval status with the retrieval status assumption, and selectively generate another access plan for the database query using the current retrieval status based upon the comparison of the current retrieval status with the retrieval status assumption.

20. The apparatus of claim 11, wherein the program code is configured to determine the retrieval status by determining whether a beginning portion of the resource is resident in the working memory, and wherein the program code is further configured to calculate a cost for the access plan based upon the determined retrieval status, and weight the cost based upon the beginning portion of the resource being resident in the working memory.