Method, computer program product, and system for rewriting database queries in a heterogenous environment

• US 6,526,403 B1
• Filed: 12/17/1999
• Issued: 02/25/2003
• Est. Priority Date: 12/17/1999
• Status: Expired due to Fees

• Current Assignee: International Business Machines Corporation
• Original Assignee: International Business Machines Corporation
• Inventors: Zhang, Tian, Lin, Eileen Tien, Mukai, Tina Louise, Venkataraman, Shivakumar
• Primary Examiner(s): Trammell, James P.
• Assistant Examiner(s): CHEUNG, MARY DA ZHI WANG
• Application Number:
  US09/466,492
• Time in Patent Office: 1,166 Days
• Field of Search: 707/1-5, 707/200, 707/203
• US Class Current: 1/1
• CPC Class Codes: G06F 16/24542 Plan optimisation
• CPC Class Codes: Y10S 707/99934 Query formulation, input preparation, or translation

Abstract

A method, computer product, and system for rewriting database without decreasing pushdownability is provided. First, a pushdown analysis of the query in its entirety is performed prior to the application of any query rewrite rules in order to establish a baseline on pushdownability for the query. The results of this analysis is stored with the internal query representation. After each rule is applied to rewrite a portion of a query, that rewritten portion is analyzed again for pushdownability. If pushdownability is not decreased, then the rewritten query remains and the internal representation of the query is updated to reflect the pushdownability of that portion. If pushdownability is decreased, then an undo operation is applied to the rewritten portion of the query to back out the effects of the rule and leave the query in the same state as before the rewrite. Finally, additional rewrite rules that are directed to the heterogeneous database environment are also provided.

Claims

1. A method for performing query rewrite in a heterogeneous database environment that preserves optimal pushdownability comprising:

storing the results of a pushdown analysis on a database query, said results including the pushdownability of portions of the database query;

rewriting a portion of the database query;

performing a pushdown analysis on the rewritten portion of the database query; and

if the pushdownability of the rewritten portion is greater than the pushdownability of the original portion, updating the stored results of the pushdown analysis to reflect the pushdownability of the rewritten portion of the database query.

2. A method as recited in claim 1 wherein the stored results of the pushdown analysis is updated if the pushdownability is greater than or equal to the pushdownability of the original portion.

3. A method as recited in claim 1 further comprising undoing the rewriting of the rewritten portion of the database so that it is the same as the original portion if the pushdownability is decreased in the rewritten portion.

4. A method as recited in claim 1 wherein the pushdown analysis comprises determining the capabilities of a remote database system by accessing information locally available.

5. A method as recited in claim 1 wherein the pushdown analysis comprises determining the capabilities of a remote database system by communicating with the remote database system.

6. A computer program product comprising:

a computer usable medium; and

computer readable instructions embodied on said computer useable medium for performing query rewrite in a heterogeneous database environment that preserves optimal pushdownability, the instructions directing a computer to perform the steps of;

storing the results of a pushdown analysis on a database query, said results including the pushdownability of portions of the database query;

rewriting a portion of the database query;

performing a pushdown analysis on the rewritten portion of the database query; and

if the pushdownability of the rewritten portion is greater than the pushdownability of the original portion, updating the stored results of the pushdown analysis to reflect the pushdownability of the rewritten portion of the database query.

7. A computer program product as recited in claim 6 wherein the stored results of the pushdown analysis is updated if the pushdownability is greater than or equal to the pushdownability of the original portion.

8. A computer program product as recited in claim 6 further comprising undoing the rewriting of the rewritten portion of the database so that it is the same as the original portion if the pushdownability is decreased in the rewritten portion.

9. A computer program product as recited in claim 6 wherein the pushdown analysis comprises determining the capabilities of a remote database system by accessing information locally available.

10. A computer program product as recited in claim 6 wherein the pushdown analysis comprises determining the capabilities of a remote database system by communicating with the remote database system.

11. A system for performing query rewrite in a heterogeneous database environment that preserves optimal pushdownability comprising:

means for storing the results of a pushdown analysis on a database query, said results including the pushdownability of portions of the database query;

means for rewriting a portion of the database query;

means for performing a pushdown analysis on the rewritten portion of the database query; and

if the pushdownability of the rewritten portion is greater than the pushdownability of the original portion, means for updating the stored results of the pushdown analysis to reflect the pushdownability of the rewritten portion of the database query.

12. A system as recited in claim 11 wherein the stored results of the pushdown analysis is updated by the means for updating if the pushdownability is greater than or equal to the pushdownability of the original portion.

13. A system as recited in claim 11 further comprising means for undoing the rewriting of the rewritten portion of the database so that it is the same as the original portion if the pushdownability is decreased in the rewritten portion.

14. A system as recited in claim 11 wherein the pushdown analysis comprises means for determining the capabilities of a remote database system by a means for accessing information locally available.

15. A system as recited in claim 11 wherein the pushdown analysis comprises a means for determining the capabilities of a remote database system by a means for communicating with the remote database system.

16. A method for performing query rewrite in a heterogeneous database environment that preserves optimal pushdownability comprising:

rewriting a portion of the database query;

performing a pushdown analysis on the rewritten portion of the database query; and

if the pushdownability of the rewritten portion is less than the pushdownability of the original portion, undoing the rewrite of the portion of the database query.

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