Query plan editor with integrated optimizer

• US 20070033159A1
• Filed: 08/03/2005
• Published: 02/08/2007
• Est. Priority Date: 08/03/2005
• Status: Abandoned Application

• Current Assignee: International Business Machines Corporation
• Original Assignee: International Business Machines Corporation
• Inventors: Cherkauer, Kevin
• Application Number:
  US11/195,957
• Publication Number:
  US 20070033159A1
• Time in Patent Office: Days
• US Class Current: 1/1
• CPC Class Codes: G06F 16/217 Database tuning G06F16/2282 takes precedence; database performance monitoring G06F11/3409
• CPC Class Codes: G06F 16/2453 Query optimisation
• CPC Class Codes: G06F 16/28 Databases characterised by their database models, e.g. relational or object models

Abstract

A tool and method for integrating manual instructions of a database query plan with a database optimizer. The tool may be in the form of an editor to receive manual instructions associated with selection of database objects such as tables and operations associated with the objects. The editor may consult with the database optimizer prior to submitting query plan execution instructions. The consultation may result in the optimizer providing alternatively available selections to the editor and/or a cost estimate for selected operations and/or automatic selection of operators to complete a plan that has been partially constructed or edited manually. Following completion of the query plan, the editor may submit the query plan to the optimizer for execution and/or save the plan for use in future execution(s) of the query.

Claims

1. A database system comprising:

an optimizer; and

an editor in communication with said optimizer;

said editor adapted to receive a manual instruction to create a query plan and to communicate said manual instruction to said optimizer, wherein said editor is adapted to receive a selection of available objects and operations from said optimizer, in response to receipt of said manual instruction.

2. The tool of claim 1, further comprising an execution instruction adapted to be submitted to said optimizer for execution of a completed query plan.

3. The tool of claim 1, wherein an operation provided by said optimizer may be directly substituted in place of a current operation.

4. The tool of claim 3, wherein substitution of an operation may change a structure of said plan.

5. The tool of claim 1, further comprising a communication device adapted to display construction of said query plan to said user.

6. The tool of claim 1, further comprising a cost estimate for each available operation adapted to be communicated from said optimizer.

7. The tool of claim 1, wherein said plan is selected from a group consisting of:

a complete plan, a partially constructed plan, a prior plan, and combinations thereof.

8. A method for creating a query plan for a database, comprising:

integrating manual instructions for creating said query plan for execution with a database optimizer;

communicating a selection of available operations and associated cost estimate for each available operation from said optimizer; and

completing said query plan for execution based upon said selection of available operations.

9. The method of claim 8, further comprising selecting an operation communicated by said optimizer in place of previously selected operation.

10. The method of claim 9, wherein the step of selecting an operation may change a structure of said plan.

11. The method of claim 8, further comprising displaying construction of said query plan.

12. The method of claim 8, wherein said plan is selected from a group consisting of:

a complete plan, a partially constructed plan, a prior plan, and combinations thereof.

13. The method of claim 8, wherein the step of completing said query plan is selected from a group consisting of:

manual and automated.

14. A computer program product comprising:

a computer useable medium having computer useable program code for creating a query plan for a database, said computer program product including;

computer useable program code for integrating instructions received for creating said query plan for execution with a database optimizer;

computer useable program code for communicating a selection of available operations and associated cost estimate for each available operation from said optimizer; and

computer useable program code for completing said query plan for execution based upon communication of said available operations.

15. The computer program product of claim 14, wherein said computer useable program code for completing said query plan includes code for substituting an operation communicated by said optimizer in place of previously selected operation.

16. The computer program product of claim 15, wherein said computer code for substituting an operation may change a structure of said plan.

17. The computer program product of claim 14, further comprising computer program code for displaying construction of said query plan.

18. The computer program product of claim 14, wherein said plan is selected from a group consisting of:

a complete plan, a partially constructed plan, a prior plan, and combinations thereof.

19. The computer program product of claim 14, wherein said computer useable code for completing said query is selected from a group consisting of:

manual and automated.

20. The computer program product of claim 14, further comprising computer program code for submission to said optimizer for execution of a completed query plan.