Determining the optimal access path for a query at execution time using an actual value for each variable in a query for estimating a filter factor

• US 6,006,220 A
• Filed: 09/30/1997
• Issued: 12/21/1999
• Est. Priority Date: 09/30/1997
• Status: Expired due to Term

• Current Assignee: International Business Machines Corporation
• Original Assignee: International Business Machines Corporation
• Inventors: Nakagawa, Randy Mitchell, Mukai, Jerry, Tie, Hong Sang, Haderle, Donald J.
• Primary Examiner(s): Lintz, Paul R.
• Assistant Examiner(s): Colbert, Ella
• Application Number:
  US08/940,245
• Time in Patent Office: 812 Days
• Field of Search: 707/4, 707/2, 707/3, 707/100, 707/103, 707/104
• US Class Current: 1/1
• 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 step of:

determining an optimal access path for the query at execution time using an actual value for each variable in the query to estimate a filter factor, wherein each variable is a special register.

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 step of:

determining an optimal access path for the query at execution time using an actual value for each variable in the query to estimate a filter factor, wherein the query is a static SQL statement and the step of determining an optimal access path further comprises the step of rebinding the static SQL statement.

3. The method of claim 2 above, wherein the SQL statement is a cursor SQL statement and the step of rebinding further comprises the step of rebinding on processing of an open command.

4. The method of claim 2 above, wherein the SQL statement is a non-cursor SQL statement and the step of rebinding further comprises the step of rebinding on processing of an execute command.

5. 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 step of:

determining an optimal access path for the query at execution time using an actual value for each variable in the query to estimate a filter factor, wherein the query is a dynamic SQL statement, and further comprising deferring the step of preparation of the dynamic SQL statement until an execute command is processed.

6. 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 step of:

determining an optimal access path for the query at execution time using an actual value for each variable in the query to estimate a filter factor, wherein the query is a dynamic SQL statement, and further comprising the step of deferring preparation of the dynamic SQL statement until a fetch command is processed.

7. 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 step of:

determining an optimal access path for the query at execution time using an actual value for each variable in the query to estimate a filter factor, wherein the query is a dynamic SQL statement, further comprising the step of deferring preparation of the dynamic SQL statement until an open command is processed.

8. 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 determining an optimal access path for the query at execution time using an actual value for each variable in the query to estimate a filter factor, wherein each variable is a special register.

9. 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 determining an optimal access path for the query at execution time using an actual value for each variable in the query to estimate a filter factor, wherein the query is a static SQL statement and the means for determining an optimal access path further comprises the means for rebinding the static SQL statement.

10. The apparatus of claim 9 above, wherein the SQL statement is a cursor SQL statement and the means for rebinding further comprises the means for rebinding on processing of an open command.

11. The apparatus of claim 9 above, wherein the SQL statement is a non-cursor SQL statement and the means for rebinding further comprises the means for rebinding on processing of an execute command.

12. 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 determining an optimal access path for the query at execution time using an actual value for each variable in the query to estimate a filter factor, wherein the query is a dynamic SQL statement, and further comprising the means for deferring preparation of the dynamic SQL statement until an execute command is processed.

13. 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 determining an optimal access path for the query at execution time using an actual value for each variable in the query to estimate a filter factor, wherein the query is a dynamic SQL statement, and further comprising the means for deferring preparation of the dynamic SQL statement until a fetch command is processed.

14. 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 determining an optimal access path for the query at execution time using an actual value for each variable in the query to estimate a filter factor, wherein the query is a dynamic SQL statement, and further comprising the means for deferring preparation of the dynamic SQL statement until an open command is processed.

15. 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 step of:

determining an optimal access path for the query at execution time using an actual value for each variable in the query to estimate a filter factor, wherein each variable is a special register.

16. 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 step of:

determining an optimal access path for the query at execution time using an actual value for each variable in the query to estimate a filter factor, wherein the query is a static SQL statement and the step of determining an optimal access path further comprises the step of rebinding the static SQL statement.

17. The method of claim 16 above, wherein the SQL statement is a cursor SQL statement and the step of rebinding further comprises the step of rebinding on processing of an open command.

18. The method of claim 16 above, wherein the SQL statement is a non-cursor SQL statement and the step of rebinding further comprises the step of rebinding on processing of an execute command.

19. 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 step of:

determining an optimal access path for the query at execution time using an actual value for each variable in the query to estimate a filter factor, wherein the query is a dynamic SQL statement, and further comprising deferring the step of preparation of the dynamic SQL statement until an execute command is processed.

20. 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 step of:

determining an optimal access path for the query at execution time using an actual value for each variable in the query to estimate a filter factor, wherein the query is a dynamic SQL statement, and further comprising the step of deferring preparation of the dynamic SQL statement until a fetch command is processed.

21. 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 step of:

determining an optimal access path for the query at execution time using an actual value for each variable in the query to estimate a filter factor, wherein the query is a dynamic SQL statement, and further comprising the step of deferring preparation of the dynamic SQL statement until an open command is processed.