Method and apparatus for optimizing a merge-join operation across heterogeneous databases

• US 5,903,893 A
• Filed: 09/15/1997
• Issued: 05/11/1999
• Est. Priority Date: 09/15/1997
• Status: Expired due to Fees

• Current Assignee: International Business Machines Corporation
• Original Assignee: International Business Machines Corporation
• Inventors: Lin, Eileen Tien, Kleewein, James Charles, Maheshwari, Hemant
• Primary Examiner(s): Choules, Jack M.
• Application Number:
  US08/931,404
• Time in Patent Office: 603 Days
• Field of Search: 707/10
• US Class Current: 707/714
• CPC Class Codes: G06F 16/2456 Join operations

Abstract

An improved join operation is performed between data in at least two tables, with one of the tables stored in a remote database (hereafter "remote table") and another table stored in a local database (hereafter "local table"). The entry values in both the local table and the remote table are accessible in an ordered manner, based upon an order of the entry values. The method of the join operation generally includes the steps of: determining a count of a number of entry values that reside in the local table that are to be joined with entry values in the remote table; comparing the count with a threshold value, and (i) if the count exceeds the threshold value, issuing a query to the remote table to fetch a range of entry values therefrom, the range determined by the range of entry values to be joined from the local table; and (ii) if the count does not exceed the threshold value, issuing a query to the remote table to fetch each specific entry value that matches the entry values to be joined from the local table.

Claims

1. A method for performing an enhanced join operation at a local processor between data from at least two tables, one table stored in a remote database (hereafter "remote table") and a second table stored in a second database (hereafter "local table") in association with said local processor, said method comprising the steps of:

a) during a compile time, analyzing (i) a received query, (ii) data relevant to said local table and said remote table and (iii) data entries to be joined from said local table, and providing a recommended predicate to be dispatched to said remote table in response to said received query, a first predicate listing each data value to be accessed from said remote table and a second predicate defining a range of data values to be accessed from said remote table;

b) during a run time, determining if a count of a number of data values in the local table to be joined with data values in the remote table exceeds or does not exceed a threshold value; and

(i) if the count exceeds the threshold value and said a second predicate is recommended, employing said second predicate in a query to a database management system (DBMS) which controls the remote table to access a range of data values and associated data therefrom; and

(ii) if the count does not exceed the threshold value and said first predicate is recommended, employing said first predicate in a query to said DBMS to access a specific list of data values and associated data from said remote table.

2. The method as recited in claim 1, wherein said range in said second predicate is determined by the range of data values to be joined from the local table.

3. The method as recited in claim 1, wherein said specific list of data values in said first predicate are determined from data values to be joined from the local table.

4. The method as recited in claim 1, wherein said count is a number of specific data values that can be accommodated by a standard query language (SQL) query with said first predicate.

5. The method as recited in claim 1, wherein step (a) comprises the following additional steps:

determining if said DBMS which controls said remote table and a DBMS which controls said local table utilize different collating sequences; and

if yes, requiring that said first predicate be employed in said query.

6. The method as recited in claim 5, comprising the further step of:

if identical collating sequences are found to be utilized in each said DBMS, and either a number of rows in the local table exceeds a predetermined threshold, or said first predicate which lists specific entries to be joined exceeds a query statement length, recommending that said second predicate be used.

7. The method as recited in claim 6, comprising the further step of:

if identical collating sequences are found to be utilized in each said DBMS, and (i) if either a number of rows in the local table does not exceed a predetermined threshold, or (ii) said first predicate which lists specific entries to be joined does not exceed a query statement length, and (iii) the local table includes a greater number of rows than the remote table, then recommending that said first predicate be used.

8. A memory media for controlling a local processor to perform an enhanced join operation between data from at least two tables, one table stored in a remote database (hereafter "remote table") and a second table stored in a second database (hereafter "local table") in association with said local processor, said memory media comprising:

a) means operative during a compile time to operate said local processor to analyze (i) a received query, (ii) data relevant to said local table and said remote table and (iii) data entries to be joined from said local table, and to provide a recommended predicate to be dispatched to said remote table in response to said received query, a first predicate listing each data value to be accessed from said remote table and a second predicate defining a range of data values to be accessed from said remote table;

b) means operative during a run time to operate said local processor to determine if a count of a number of data values in the local table to be joined with data values in the remote table exceeds or does not exceed a threshold value; and

(i) if the count exceeds the threshold value and said second predicate is recommended, to cause said local processor to employ said second predicate in a query for dispatch to a database management system (DBMS) which controls the remote table to access a range of data values and associated data therefrom; and

(ii) if the count does not exceed the threshold value and said first predicate is recommended, to cause said local processor to employ said first predicate in a query for dispatch to said DBMS to access a specific list of data values and associated data from said remote table.

9. The memory media as recited in claim 8, wherein said range in said second predicate is determined by the range of data values to be joined from the local table.

10. The memory media as recited in claim 8, wherein said specific list of data values in said first predicate are determined from data values to be joined from the local table.

11. The memory media as recited in claim 8, wherein said count is a number of specific data values that can be accommodated by a standard query language (SQL) query with a first predicate.

12. The memory media as recited in claim 8, wherein means (a) controls said local processor to determine if said DBMS which controls said remote table and a DBMS which controls said local table utilize different collating sequences, and if yes, to require that said first predicate be employed in said query.

13. The memory media as recited in claim 12, wherein means a) controls said local processor to recommend that said second predicate be used (i) if identical collating sequences are found to be utilized in each said DBMS, and either (ii) a number of rows in the local table exceeds a predetermined threshold, or (iii) said first predicate which lists specific entries to be joined exceeds a query statement length.

14. The memory media as recited in claim 13, wherein means a) controls said local processor to recommend that said first predicate be used (i) if identical collating sequences are found to be utilized in each said DBMS, and (ii) if either a number of rows in the local table does not exceed a predetermined threshold, or (iii) said first predicate which lists specific entries to be joined does not exceed a query statement length, and (iv) the local table includes a greater number of rows than the remote table.