Method, computer program product, and system for pushdown analysis during query plan generation

• US 6,615,203 B1
• Filed: 12/17/1999
• Issued: 09/02/2003
• Est. Priority Date: 12/17/1999
• Status: Expired due to Term

• Current Assignee: International Business Machines Corporation
• Original Assignee: International Business Machines Corporation
• Inventors: Zhang, Tian, Lin, Eileen Tien, Venkataraman, Shivakumar, Mukai, Tina Louise
• Primary Examiner(s): Metjahic, Safet
• Assistant Examiner(s): AL HASHEMI, SANA A
• Application Number:
  US09/466,560
• Time in Patent Office: 1,355 Days
• Field of Search: 707/2, 707/3, 707/6, 707/7, 707/100, 707/101, 707/102
• US Class Current: 1/1
• CPC Class Codes: G06F 16/24542 Plan optimisation
• CPC Class Codes: Y10S 707/99933 Query processing, i.e. searching

Abstract

A method, computer product, and system for pushdown analysis during query plan generation is provided. Rather than do pushdown analysis for portions of a query that are query execution plan independent, the present invention provides pushdown analysis for features of a query that are plan dependent. This is done by evaluating a portion of the query plan for pushdownability to a remote database system; and if pushdownable to the remote database system, generating cost parameters for the portion of the plan if executed at a local database system and if executed at the remote database system. With both sets of cost parameters, the query optimizer has added information on which to select an optimal query plan.

Claims

1. A method for query optimization in a heterogeneous database system utilizing pushdown analysis comprising:

generating a portion of a query plan;

evaluating the portion of the query plan for pushdownability to a remote database system; and

if pushdownable to the remote database system, generating cost parameters for said portion of the plan if executed at a local database system and if executed at the remote database system, the cost parameters adapted for use in determining an optimal query plan.

2. A method as recited in claim 1 further comprising, prior to generation of query plans, evaluating the entire query for portions that are pushdownable to one or more remote database systems independent of plan enumeration.

3. A method as recited in claim 1 wherein evaluation of the portion of the query plan for pushdownability occurs at a remote database system.

4. A method as recited in claim 1 wherein evaluation of the portion of the query plan for pushdownability occurs at a local database system.

5. A method as recited in claim 1 wherein evaluation of the portion of the query plan for pushdownability occurs at a local database system and comprises accessing at the local database system information summarizing the capabilities of one or more remote database systems.

6. A method as recited in claim 1 wherein evaluation of the portion of the query plan for pushdownability occurs at a local database system and comprises communicating with one or more remote database systems to determine the capabilities of the remote database systems.

7. A computer program product comprising:

a computer usable medium; and

computer readable instructions embodied on said computer useable medium for query optimization in a heterogeneous database system utilizing pushdown analysis, the instructions directing a computer to perform the steps of;

generating a portion of a query plan;

evaluating the portion of the query plan for pushdownability to a remote database system; and

if pushdownable to the remote database system, generating cost parameters for said portion of the plan if executed at a local database system and if executed at the remote database system, the cost parameters adapted for use in determining an optimal query plan.

8. A computer program product as recited in claim 7 further comprising, prior to generation of query plans, evaluating the entire query for portions that are pushdownable to one or more remote database systems independent of plan enumeration.

9. A computer program product as recited in claim 7 wherein evaluation of the portion of the query plan for pushdownability occurs at a remote database system.

10. A computer program product as recited in claim 7 wherein evaluation of the portion of the query plan for pushdownability occurs at a local database system.

11. A computer program product as recited in claim 7 wherein evaluation of the portion of the query plan for pushdownability occurs at a local database system and comprises accessing at the local database system information summarizing the capabilities of one or more remote database systems.

12. A computer program product as recited in claim 7 wherein evaluation of the portion of the query plan for pushdownability occurs at a local database system and comprises communicating with one or more remote database systems to determine the capabilities of the remote database systems.

13. A system for query optimization in a heterogeneous database system utilizing pushdown analysis comprising:

means for generating a portion of a query plan;

means for evaluating the portion of the query plan for pushdownability to a remote database system; and

if pushdownable to the remote database system, means for generating cost parameters for said portion of the plan if executed at a local database system and if executed at the remote database system, the cost parameters adapted for use in determining an optimal query plan.

14. A system as recited in claim 13 further comprising, prior to generation of query plans, means for evaluating the entire query for portions that are pushdownable to one or more remote database systems independent of plan enumeration.

15. A system as recited in claim 13 wherein evaluation of the portion of the query plan for pushdownability occurs at a remote database system.

16. A system as recited in claim 13 wherein evaluation of the portion of the query plan for pushdownability occurs at a local database system.

17. A system as recited in claim 13 wherein evaluation of the portion of the query plan for pushdownability occurs at a local database system and comprises means for accessing at the local database system information summarizing the capabilities of one or more remote database systems.

18. A system as recited in claim 13 wherein evaluation of the portion of the query plan for pushdownability occurs at a local database system and comprises means for communicating with one or more remote database systems to determine the capabilities of the remote database systems.

19. A method for query optimization in a heterogeneous database system utilizing pushdown analysis comprising:

processing, at a local system, incoming queries by building portions of a query plan and combining the portions to create final query plans that are assessed to determine the optimal query plan;

while generating each query plan, evaluating a portion of the plan for pushdownability to a remote database system; and

if pushdownable to the remote database system, generating cost parameters for said portion of the plan if executed at the local database system and if executed at the remote database system, the cost parameters adapted for use in determining the optimal query plan.

20. A computer program product comprising a computer usable medium having computer-executable instructions embodied therein to perform the method as recited in claim 19.

21. In a query optimization phase that generates a plurality of different query execution plans by building portions of a plan and combining the portions to create final plans that are then assessed to determine the optimal query plan, a method for query optimization utilizing pushdown analysis comprising:

while generating each query plan, evaluating a portion of the plan for pushdownability to a remote system; and

if pushdownable the remote database system, generating cost parameters for said portion of the plan if executed at a local database system and if executed at the remote database system, the cost parameters adapted for use in determining the optimal query plan.

22. A computer program product comprising a computer usable medium having computer-executable instructions embodied therein to perform the method as recited in claim 21.