Method and apparatus for optimizing queries across heterogeneous databases

• US 5,943,666 A
• Filed: 09/15/1997
• Issued: 08/24/1999
• Est. Priority Date: 09/15/1997
• Status: Expired due to Term

• Current Assignee: International Business Machines Corporation
• Original Assignee: International Business Machines Corporation
• Inventors: Venkataraman, Shivakumar, Lin, Eileen Tien, Kleewein, James Charles, Mukai, Tina Louise
• Primary Examiner(s): Black, Thomas G.
• Assistant Examiner(s): Coby, Frantz
• Application Number:
  US08/929,877
• Time in Patent Office: 708 Days
• Field of Search: 707/2, 707/3, 707/10, 707/4
• US Class Current: 1/1
• CPC Class Codes: G06F 16/24542 Plan optimisation
• CPC Class Codes: G06F 16/2471 Distributed queries
• 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

Abstract

A heterogeneous database system includes plural databases, one database acting as a system manager which performs functions to provide a transparent user interface that encompasses both the system manager database and other databases. The system manager includes a memory which stores (i) a database management procedure, (ii) a catalog of database functions that are supported by the plural databases, and (iii) a query optimizer procedure. A processor is responsive to a user query which requires data stored at a remote database, to execute the query optimizer procedure. The query optimizer procedure employs entries from the catalogue relating to database functions supported by both the system manager database and the remote database and establishes query plans for responding to the query. A first query plan is based upon execution at the remote database and a second query plan is based upon execution at the system manager database. The query optimizer then chooses one of the query plans in accordance with a determined criteria and causes the selected plan to be executed.

Claims

1. A heterogeneous database system including plural databases, one database acting as a system manager (hereafter "system manager") which performs functions to provide a transparent user database encompassing both said one database and other databases, said system manager comprising:

a memory for storing (i) a database management procedure, (ii) a catalogue of database functions that are supported by said plural databases, including but not limited to join methods, aggregate functions and search algorithms, and (iii) a query optimizer procedure; and

a processor coupled to said memory and responsive to a user query which requires execution of a database function with respect to data stored at a first database, for executing said query optimizer procedure, said processor, under control of said query optimizer procedure, employing entries from said catalogue relating to database functions supported by both said first database and a second database, to establish query plans for responding to said query, a first said query plan based upon an optimized execution thereof at said first database and a second said query plan based upon an optimized execution thereof at said second database, and for choosing one of said query plans in accordance with a determined criteria, to be executed by said database management procedure in response to said user query.

2. The heterogeneous database system as recited in claim 1, wherein said processor, under control of said query optimizer procedure, employs entries from said catalogue relating to functions supported by said first database, to emulate a query optimization procedure for said first database, approximately as said query optimization procedure is executed by said first database.

3. The heterogeneous database system as recited in claim 1, wherein said determined criteria for choosing one of said query plans is an execution cost thereof.

4. The heterogeneous database system as recited in claim 1, wherein said catalogue includes, for each database type, entries which define supported functions, said supported functions at least including:

a search algorithm or algorithms used in said database type;

a data access method or methods used in said database type; and

join methods used by said database type.

5. The heterogeneous database system as recited in claim 1, wherein said second database is the database that acts as said system manager.

6. The heterogeneous database system as recited in claim 5, wherein said processor employs one of said query plans, chosen in accord with said determined criteria, to access data from one of said plural databases and to present said data to a user processor as though said data had come from the database that acts as said system manager, whether or not said data came therefrom or from said first database.

7. The heterogeneous database system as recited in claim 1, wherein said first database is remotely positioned from said system manager.

8. A memory media including software procedures to provide transparent access to plural heterogeneous databases, one of said databases acting as a system manager (hereafter "system manager") which performs functions of a transparent user database encompassing both said one of said databases and other databases, said memory media comprising:

a) means for causing said system manager to store (i) a database management procedure, (ii) a catalogue of database functions that are supported by said plural databases, including but not limited to join methods, aggregate functions and search algorithms, and (iii) a query optimizer procedure; and

b) means for controlling a processor in said system manager to be responsive to a user query which requires execution of a database function with respect to data stored at a first database, to execute said query optimizer procedure, said query optimizer procedure employing entries from said catalogue relating to database functions supported by both said system manager and said first database, by establishing query plans for responding to said query, a first said query plan based upon an optimized execution thereof at said first database and a second said query plan based upon an optimized execution thereof at said system manager, and for choosing one of said query plans in accordance with a determined criteria, to be executed by said database management procedure in response to said user query.

9. The memory media as recited in claim 8, wherein said means b) causes said processor to employ entries from said catalogue relating to functions supported by said first database, to emulate a query optimization procedure for said first database approximately as said query optimization procedure is executed by said first database.

10. The memory media as recited in claim 8, wherein said means b) causes said processor to utilize execution cost as said determined criteria for choosing one of said query plans.

11. The memory media as recited in claim 8, wherein the catalogue of means a) includes, for each database type, entries which define supported functions, said supported functions including:

a search algorithm or algorithms used in said database type;

a data access method or methods used in said database type; and

join methods used by said database type.

12. The memory media as recited in claim 8, wherein said means b) causes said processor to employ one of said query plans, chosen in accord with said determined criteria, to access data from one of said plural databases and to present said data to a user processor as though said data had come from said system manager database, whether or not said data came from said system manager database or said first database.

13. The memory media as recited in claim 8, wherein said first database is remotely positioned from said system manager.

14. A method for choosing an optimized query plan for accessing data in a heterogeneous database system, one database acting as a system manager (hereafter "system manager") which performs functions of a transparent user database, said method implemented by said system manager and comprising the steps of:

a) storing (i) a database management procedure, (ii) a catalogue of database functions that are supported by said plural databases, including but not limited to join methods, aggregate functions and search algorithms, and (iii) a query optimizer procedure;

b) responding to a user query which requires execution of a database function with respect to data stored at a first database, by executing said query optimizer procedure, said query optimizer procedure employing entries from said catalogue relating to database functions supported by both said first database and a second database, and establishing query plans for responding to said query, a first said query plan based upon an optimized execution thereof at said first database and a second said query plan based upon an optimized execution thereof at said second database;

c) choosing one of said query plans in accordance with a determined criteria; and

d) executing said database management procedure, using a query plan chosen in step c) in response to said user query.

15. The method as recited in claim 14, wherein step b) employs entries from said catalogue relating to functions supported by said first database, and emulates a query optimization procedure for said first database approximately as said query optimization procedure is executed by said first database.

16. The method as recited in claim 14, wherein step c) employs execution cost as said determined criteria for choosing one of said query plans.

17. The method as recited in claim 14, wherein said second database is the database that acts as said system manager.

18. The method as recited in claim 17, wherein step d) employs one of said query plans, chosen in accord with said determined criteria, to access data from one of said plural databases and to present said data to a user processor as though said data had come from said database that acts as said system manager, whether or not said data came therefrom.