Joining Tables in Multiple Heterogeneous Distributed Databases

• US 20100082671A1
• Filed: 09/23/2009
• Published: 04/01/2010
• Est. Priority Date: 09/26/2008
• Status: Active Grant

• Current Assignee: International Business Machines Corporation
• Original Assignee: International Business Machines Corporation
• Inventors: Sun, Yun Feng, Li, Ming, Li, Hai Feng, Zhao, Sheng
• Granted Patent: US 8,122,008 B2
• Time in Patent Office: Days
• US Class Current: 707/770
• CPC Class Codes: G06F 16/2456 Join operations

Abstract

A method for joining tables in multiple heterogeneous distributed databases implemented by at least two data sources accessible to a federal database server over a network includes: transmitting from the federated database server a sub-command to a first of the data sources responsive to the federated database server receiving a data query; retrieving, with the federated database server, block data from the first data source related to the data query using block fetching according to the sub-command; transmitting, with the federated database server, at least a portion of the block data to a second of the data sources together with an instruction for the second data source to perform a join operation on the portion of the block data and a data table stored by the second data source related to the query; and retrieving a result of the join operation with the federated database server.

Claims

1. A method for joining tables in multiple heterogeneous distributed databases implemented by at least two data sources accessible to a federated database server over a network, said method comprising:

transmitting from said federated database server a sub-command to a first of said data sources responsive to said federated database server receiving a data query;

retrieving, with said federated database server, block data from said first data source related to said data query using block fetching according to said sub-command;

transmitting, with said federated database server, at least a portion of said block data to a second of said data sources together with an instruction for said second data source to perform a join operation on said portion of said block data and a data table stored by said second data source related to said query; and

retrieving a result of said join operation with said federated database server.

2. The method according to claim 1, further comprising determining a size of said block data prior to said retrieving said block data from said first data source.

3. The method according to claim 2, in which said determining said size of said block data further comprises:

calculating a first maximal row number by dividing a maximal memory size in said second data source available for performing said join operation by a maximal row size of a temporary table to be established in said second data source;

calculating a second maximal row number by dividing a maximal memory size of an available local cache in said federated database server by a maximal row size of said block data; and

assigning a smaller of said first and second maximal row numbers as a readable row number for said block data.

4. The method according to claim 1, further comprising:

responsive to a size of non-related columns in said block data read from said first data source being larger than a size of a row pointer column, replacing columns in said block data unrelated to said join operation with a row pointer column when said block data is transmitted to said second data source.

5. The method according to claim 4, further comprising:

according to said row pointer column, directly merging, in said federated database server, said result of said join operation with said block data retrieved from said first data source to form a merged table.

6. The method according to claim 4, further comprising:

responsive to a size of non-related columns in said block data read from said first data source being smaller than said size of said row pointer column, transmitting all columns in said block data to said second data source for said join operation.

7. The method according to claim 6, further comprising:

directly storing said result of said join operation received from said second data source as a merged table.

8. The method according to claim 1, wherein said join operation comprises a hash join operation.

9. The method according to claim 1, wherein said join operation comprises a block nested loop join operation.

10. A system for joining tables in multiple heterogeneous distributed databases, said system comprising:

a federated database server configured to be communicatively coupled to a first and second data sources over a network;

in which said federated database server is configured to;

transmit a sub-command to said first data source responsive to receiving a data query;

retrieve block data from said first data source related to said data query using block fetching according to said sub-command;

transmit at least a portion of said block data to said second data source together with an instruction for said second data source to perform a join operation on said portion of said block data and a data table stored by said second data source related to said query; and

retrieve a result of said join operation from said second data source.

11. The system according to claim 10, in which said federated database server is further configured to determine a size of said block data prior to retrieving said block data from said first data source.

12. The system according to claim 11, in which said federated database server is configured to determine said size of said block data by:

calculating a first maximal row number by dividing a maximal memory size in said second data source available for performing said join operation by a maximal row size of a temporary table to be established in said second data source;

calculating a second maximal row number by dividing a maximal memory size of an available local cache in said federated database server by a maximal row size of said block data; and

assigning the smaller of said first and second maximal row numbers as a readable row number for said block data.

13. The system according to claim 10, in which said federated database server is configured to, responsive to a size of non-related columns in said block data read from said first data source being larger than a size of a row pointer column, replace columns in said block data unrelated to said join operation with a row pointer column when said block data is transmitted to said second data source.

14. The system according to claim 13, in which said federated database server is further configured to, according to said row pointer column, directly merge said result of said join operation with said block data retrieved from the first data source to form a merged table.

15. The system according to claim 13, in which said federated database server is further configured to:

responsive to a size of non-related columns in said block data read from said first data source being smaller than said size of said row pointer column, transmit all columns in said block data to said second data source for said join operation.

16. The system according to claim 15, in which said federated database server is further configured to directly store said result of said join operation received from the second data source as a merged table.

17. The system according to claim 10, wherein said join operation comprises a hash join operation.

18. The system according to claim 10, wherein said join operation comprises a block nested loop join operation.

19. A computer program product for joining tables in multiple heterogeneous distributed databases implemented by at least two data sources, said computer program product comprising:

a computer readable storage medium having computer readable program code embodied therewith, the computer readable program code comprising;

computer readable program code configured to transmitting a sub-command to a first of said data sources responsive to receiving a data query;

computer readable program code configured to retrieve block data from said first data source related to said data query using block fetching according to said sub-command;

computer readable program code configured to transmit at least a portion of said block data to a second of said data sources together with an instruction for said second data source to perform a join operation on said portion of said block data and a data table stored by said second data source related to said query; and

computer readable program code configured to retrieve a result of said join operation.

20. The computer program product of claim 19, in which said computer readable program code further comprises:

computer readable program code configured to, responsive to a size of non-related columns in said block data read from said first data source being larger than a size of a row pointer column, replace columns in said block data unrelated to said join operation with a row pointer column when said block data is transmitted to said second data source; and

computer readable program code configured to transmit all columns in said block data to said second data source for said join operation responsive to a size of non-related columns in said block data read from said first data source being smaller than said size of said row pointer column.