Access path selection for SQL with variables in a RDBMS

• US 6,219,660 B1
• Filed: 09/08/1999
• Issued: 04/17/2001
• Est. Priority Date: 09/30/1997
• Status: Expired due to Term

• Current Assignee: International Business Machines Corporation
• Original Assignee: International Business Machines Corporation
• Inventors: Tie, Hong Sang, Nakagawa, Randy Mitchell, Mukai, Jerry, Haderle, Donald J.
• Primary Examiner(s): Alam, Hosain T.
• Assistant Examiner(s): Colbert, Ella
• Application Number:
  US09/392,151
• Time in Patent Office: 587 Days
• Field of Search: 707/2, 707/3, 707/4, 707/100, 707/103, 707/104
• US Class Current: 707/718
• CPC Class Codes: G06F 16/24524 Access plan code generation and invalidation; Reuse of access plans
• CPC Class Codes: G06F 16/24542 Plan optimisation
• CPC Class Codes: G06F 16/24545 Selectivity estimation or determination
• CPC Class Codes: G06F 16/24549 Run-time optimisation
• CPC Class Codes: Y10S 707/956 Hierarchical
• CPC Class Codes: Y10S 707/99931 Database or file accessing
• 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/99945 Object-oriented database structure processing

Abstract

A method, apparatus, and article of manufacture for a computer implemented query compiler. A query is executed in a computer, the query being performed by the computer to retrieve data from a database stored on a data storage device connected to the computer. The query is received containing at least one variable. At execution time, a value is determined for each variable in the query. A filter factor is estimated using the determined value for each variable. Then, an access path is determined for the query using the estimated filter factor.

Claims

1. A method of executing a query in a computer, the query being performed by the computer to retrieve data from a database stored on a data storage device connected to the computer, the method comprising the steps of:

receiving the query containing at least one variable;

determining an access path for the query during a bind process based on a default filter factor; and

at execution time optimizing the access path by, determining a value for each variable in the query;

generating an optimal filter factor using the determined value for each variable; and

generating a new access path for the query using the optimal filter factor, while disregarding any previously generated access path based on the default filter factor.

2. A method of executing a query in a computer, the query being performed by the computer to retrieve data from a database stored on a data storage device connected to the computer, the method comprising the steps of:

generating an optimal access path for the query after initial compilation and during the execution using an actual value for each variable in the query to estimate a filter factor, while disregarding any previously generated access path based on a default filter factor selected prior to determining the actual value for each variable in the query.

3. The method of claim 2 above, wherein the variable is a host variable.

4. The method of claim 2 above, wherein the variable is a parameter marker.

5. An apparatus for executing a query, comprising:

a computer having a data storage device connected thereto, wherein the data storage device stores a database;

one or more computer programs, performed by the computer, for receiving the query containing at least one variable, determining an access path for the query during a bind process based on a default filter factor, and at execution time, optimizing the access path by determining a value for each variable in the query, generating a new optimal filter factor using the determined value for each variable, and generating a new access path for the query using the optimal filter factor, while disregarding any previously generated access path based on the default filter factor.

6. An apparatus for executing a query, comprising:

a computer having a data storage device connected thereto, wherein the data storage device stores a database;

one or more computer programs, performed by the computer, for generating an optimal access path for the query after initial compilation and during the execution using an actual value for each variable in the query to estimate a filter factor, while disregarding any previously generated access path based on a default filter factor selected prior to determining the actual value for each variable in the query.

7. The apparatus of claim 6 above, wherein the variable is a host variable.

8. The apparatus of claim 6 above, wherein the variable is a parameter marker.

9. An article of manufacture comprising a computer program carrier readable by a computer and embodying one or more instructions executable by the computer to perform method steps for executing a query, the query being performed by the computer to retrieve data from a database stored in a data storage device connected to the computer, the method comprising the steps of:

receiving the query containing at least one variable; and

determining an access path for the query during a bind process based on a default filter factor; and

at execution time optimizing the access path by,

determining a value for each variable in the query;

generating an optimal filter factor using the determined value for each variable; and

generating a new access path for the query using the estimated filter factor, while disregarding any previously generated access path.

10. An article of manufacture comprising a computer program carrier readable by a computer and embodying one or more instructions executable by the computer to perform method steps for executing a query, the query being performed by the computer to retrieve data from a data base stored in a data storage device connected to the computer, the method comprising the step of:

generating an optimal access path for the query after initial compilation and during the execution using an actual value for each variable in the query to estimate a filter factor, while disregarding any previously generated access path based on a default filter factor selected prior to determining the actual value for each variable in the query.

11. The article of manufacture of claim 10 above, wherein the variable is a host variable.

12. The article of manufacture of claim 10 above, wherein the variable is a parameter marker.