# US 2005/0091196 A1 · Optimization of queries using retrieval status of resources used thereby

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

- Number: US 2005/0091196 A1
- Document ID: US20050091196A1
- Status: Published application
- Kind: A1
- Application number: 10/691,296
- Filed: 2003-10-22
- Published: 2005-04-28
- Earliest claimed: 2003-10-22
- Assignees: International Business Machines Corporation
- Inventors: Paul Day; Brian Muras
- Web page: https://patentgrep.com/patent/US20050091196A1

## CPC

- G06F 16/24542: {Plan optimisation}
- Y10S 707/99932: Access augmentation or optimizing
- Y10S 707/99933: Query processing, i.e. searching
- Y10S 707/99934: Query formulation, input preparation, or translation
- Y10S 707/99935: Query augmenting and refining, e.g. inexact access
- G06F: ELECTRIC DIGITAL DATA PROCESSING (computer systems based on specific computational models G06N)
- Y10S: TECHNICAL SUBJECTS COVERED BY FORMER USPC CROSS-REFERENCE ART COLLECTIONS [XRACs] AND DIGESTS

## 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

### Claim 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.

### Claim 2 (depends on 1)

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.

### Claim 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.

### Claim 4 (depends on 3)

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.

### Claim 5 (depends on 3)

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.

### Claim 6 (depends on 3)

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.

### Claim 7 (depends on 3)

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.

### Claim 8 (depends on 3)

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

### Claim 9 (depends on 3)

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

### Claim 10 (depends on 9)

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

### Claim 11 (depends on 10)

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.

### Claim 12 (depends on 3)

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.

### Claim 13 (depends on 12)

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.

### Claim 14 (depends on 12)

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.

### Claim 15 (depends on 3)

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.

### Claim 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.

### Claim 17 (depends on 16)

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.

### Claim 18 (depends on 16)

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.

### Claim 19 (depends on 16)

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.

### Claim 20 (depends on 16)

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.

### Claim 21 (depends on 16)

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.

### Claim 22 (depends on 16)

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.

### Claim 23 (depends on 22)

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

### Claim 24 (depends on 23)

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.

### Claim 25 (depends on 16)

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.

### Claim 26 (depends on 25)

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.

### Claim 27 (depends on 25)

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.

### Claim 28 (depends on 16)

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.

### Claim 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.

### Claim 30 (depends on 29)

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.
