Optimization of queries using retrieval status of resources used thereby

• US 20050091196A1
• Filed: 10/22/2003
• Published: 04/28/2005
• 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
• Granted Patent: US 7,308,437 B2
• Time in Patent Office: Days
• 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 performing a database query, the method comprising:

generating an access plan for the database query, the access plan using at least one resource capable of being retrieved into working memory, 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, and a hash table;

estimating a percentage of the resource that is currently resident in working memory;

estimating a cost for the access plan using the estimated percentage; and

selectively executing the access plan based upon the estimated cost.

2. The method of claim 1, further comprising generating a second access plan for the database query and estimating a cost for the second access plan, wherein selectively executing the first access plan includes comparing the estimated costs for the first and second access plan and executing the first access plan if the estimated cost therefor is less than that of the second access plan.

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

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.

4. The method of claim 3, 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.

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

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

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

8. The method of claim 3, wherein determining the retrieval status includes determining a percentage of the resource that is resident in working memory.

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

10. The method of claim 9, further comprising tracking, with the resource manager, a percentage of the resource that is in working memory.

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

12. The method of claim 3, 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.

13. The method of claim 12, wherein the retrieval status for the resource indicates a percentage of the resource that is resident in working memory, 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.

14. The method of claim 12, further comprising:

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;

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.

15. The method of claim 3, 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.

16. An apparatus, comprising:

at least one processor;

a memory that includes a working memory; 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.

17. The apparatus of claim 16, 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.

18. The apparatus of claim 16, 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.

19. The apparatus of claim 16, 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.

20. The apparatus of claim 16, 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.

21. The apparatus of claim 16, 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.

22. The apparatus of claim 16, further comprising a resource manager, wherein the program code is configured to determine the retrieval status by accessing the resource manager to obtain the retrieval status of the resource.

23. The apparatus of claim 22, wherein the resource manager is configured to track a percentage of the resource that is in the working memory.

24. The apparatus of claim 23, wherein the resource manager is further configured to store the percentage of the resource that is in working memory in a header for a persistent copy of the resource.

25. The apparatus of claim 16, 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.

26. The apparatus of claim 25, 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.

27. The apparatus of claim 25, 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.

28. The apparatus of claim 16, 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.

29. A program product, comprising:

program code configured 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; and

a computer readable signal bearing medium bearing the program code.

30. The program product of claim 29, wherein the computer readable signal bearing medium includes at least one of a transmission medium and a recordable medium.