Method and system for providing a learning optimizer for federated database systems

• US 20060195416A1
• Filed: 02/28/2005
• Published: 08/31/2006
• Est. Priority Date: 02/28/2005
• Status: Active Grant

• Current Assignee: International Business Machines Corporation
• Original Assignee: International Business Machines Corporation
• Inventors: Ewen, Stephan Eberhard, Markl, Volker Gerhard, Ortega-Binderberger, Michael
• Granted Patent: US 7,610,264 B2
• Time in Patent Office: Days
• 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/99935 Query augmenting and refining, e.g. inexact access

Abstract

A method and system for method for accelerating execution of a query on a federated database system is disclosed. The federated database system is associated with an external data source, which is used by the query. The query is performed based upon a query execution plan. The method and system include generating an optimizer query for the external data source utilized by the query. The optimizer query is based on the query and obtains data related to the external data source. The method and system further include providing the optimizer query to the external data source and collecting at least one resultant from the optimizer query for use in generating a future query execution plan.

Claims

1. A method for accelerating execution of a query on a federated database system, the federated database system being associated with an external data source, the query utilizing the external data source and being performed based upon a query execution plan, the method comprising:

generating at least one optimizer query for the external data source utilized by the query, the at least one optimizer query being based on the query and for obtaining data related to the external data source;

providing the at least one optimizer query to the external data source for execution using the external data source; and

collecting at least one resultant from execution of the at least one optimizer query for use in generating a future query execution plan.

2. The method of claim 1 further comprising:

determining, based on the query execution plan and available statistics from at least one previous optimizer query, whether the data for the external data source should be collected using the at least one optimizer query.

3. The method of claim 2 wherein the at least one optimizer query generating further includes:

generating the at least one optimizer query based on the query execution plan and the available statistics.

4. The method of claim 1 wherein the at least one optimizer query generating further includes:

parsing the query to determine a portion of the query utilizing the external data source; and

generating the at least one optimizer query based on the portion of the query utilizing the external data source.

5. The method of claim 1 wherein the external data source utilizes at least one operator to execute the query and wherein the at least one optimizer query is for determining the at least one resultant for the at least one operator.

6. The method of claim 1 further comprising:

collecting the statistics on the at least one resultant.

7. The method of claim 6 further comprising:

analyzing the statistics to provide the future query execution plan; and

providing the future query execution plan.

8. The method of claim 1 wherein the external data source includes a proprietary monitoring tool and wherein the at least one optimizer query utilizes the proprietary monitoring tool in order to obtain the statistics.

9. The method of claim 1 wherein the at least one optimizer query includes at least one of a user-defined table function and a count statement.

10. The method of claim 1 wherein the at least one optimizer query further is configured to perform sampling on the external data source.

11. The method of claim 1 wherein the providing step further includes:

providing the at least one optimizer query to the external data source at a time based on the query being provided.

12. The method of claim 11 wherein the at least one optimizer query is provided to the external data source at substantially the same time as the query.

13. The method of claim 1 wherein the providing step further includes:

deferring the at least one optimizer query from being provided to the external data.

14. The method of claim 13 wherein the providing step further includes:

providing the at least one optimizer query to the external data source based on a usage of the external data source.

15. A system for accelerating execution of a query on a federated database system, the federated database system being associated with an external data source, the query utilizing the external data source and being performed based upon a query execution plan, the system comprising:

an optimizer query generator for generating at least one optimizer query for the external data source utilized by the query, the at least one optimizer query being based on the query and for obtaining data related to the external data source, the at least one optimizer query being provided to the external data source for execution;

a query feedback warehouse for storing statistics from execution of the at least one optimizer query, the statistics for use in generating a future query execution plan; and

a monitor for collecting the at least one resultant from execution the at least one optimizer query.

16. The system of claim 15 further comprising:

a query feedback analyzer for determining, based on the query execution plan and at least one previous optimizer query, whether the at least one resultant for the external data source should be collected using the at least one optimizer query.

17. The system of claim 15 wherein the optimizer query generator further generates the at least one optimizer query based on available statistics for the external data source.

18. The system of claim 15 wherein the optimizer query generator further parses the query to determine a portion of the query utilizing the external data source and generates the at least one optimizer query based on the portion of the query utilizing the external data source.

19. The system of claim 15 wherein the external data source utilizes at least one operator to execute the query and wherein the at least one optimizer query is used in determining the statistics for the at least one operator.

20. A computer-readable medium containing a program for accelerating execution of a query on a federated database system, the federated database system being associated with an external data source, the query utilizing the external data source and being performed based upon a query execution plan, the program including instructions for:

generating at least one optimizer query for the external data source utilized by the query, the at least one optimizer query being based on the query and for obtaining data related to the external data source;

providing the at least one optimizer query to the external data source for execution using the external data source; and

collecting statistics from execution of the at least one optimizer query for use in generating a future query execution plan.