Branching optimization in a multi-database system

• US 8,434,075 B1
• Filed: 04/15/2009
• Issued: 04/30/2013
• Est. Priority Date: 04/15/2009
• Status: Active Grant

• Current Assignee: Teradata US, Inc. (Teradata Corporation)
• Original Assignee: Teradata US, Inc. (Teradata Corporation)
• Inventors: Brown, Douglas P., Morris, John Mark
• Primary Examiner(s): Dao, Thuy
• Assistant Examiner(s): Nguyen, Mongbao
• Application Number:
  US12/423,885
• Time in Patent Office: 1,476 Days
• Field of Search: 707/713, 707/718
• US Class Current: 717/151
• CPC Class Codes: G06F 16/24542 Plan optimisation

Abstract

Execution of a query is optimized. A plurality of plans for executing the query on a multi-database system are analyzed. A first plan having a first step and a second step is selected. The first step of the first plan is executed. The plan selection is re-evaluated before executing the second step of the first plan. A second plan of the plurality of plans for executing the query is executed to produce a result. The result is stored.

Claims

1. A method comprising:

evaluating a plurality of plans for executing a query on a multi-database system, the plurality of plans including a first plan and a second plan different from the first plan, and, as a result of evaluating, selecting a first plan, the first plan having a first step and a second step, the second plan having a first step and a second step, the first step of the first plan being the same as the first step of the second plan, and the second step of the first plan being different from the second step of the second plan;

executing the first step of the first plan, after evaluating the plurality of plans and selecting the first plan;

re-evaluating the plan selection after executing the first step of the first plan but before executing the second step of the first plan and, as a result of re-evaluating, selecting the second plan to continue executing the query;

executing a second step of the second plan to produce a result; and

storing the result.

2. The method of claim 1 wherein selecting the first plan comprises:

selecting the one of the plurality of plans that has the least cost.

3. The method of claim 1 wherein:

the second plan has a first step and a second step; and

executing the second plan comprises executing the first step of the second plan and the second step of the second plan.

4. The method of claim 1 further comprising:

determining that the first plan is no longer viable; and

, in response selecting the second plan to execute the query.

5. The method of claim 4 wherein determining that the first plan is no longer viable comprises:

determining that a system resource necessary to perform the first plan has malfunctioned.

6. The method of claim 4 wherein determining that the first plan is no longer viable comprises:

determining that a system resource necessary to perform the first plan has become busy.

7. A multi-database system comprising:

one or more nodes;

a plurality of CPUs, each of the one or more nodes providing access to one or more CPUs;

a plurality of virtual processes, each of the one or more CPUs providing access to one or more virtual processes;

each virtual process configured to manage data, including rows from the set of database table rows, stored in one of a plurality of data-storage facilities;

a process for;

evaluating a plurality of plans for executing a query on a multi-database system, the plurality of plans including a first plan and a second plan different from the first plan, and, as a result of evaluating, selecting a first plan, the first plan having a first step and a second step, the second plan having a first step and a second step, the first step of the first plan being the same as the first step of the second plan, and the second step of the first plan being different from the second step of the second plan;

executing the first step of the first plan, after evaluating the plurality of plans and selecting the first plan;

re-evaluating the plan selection after executing the first step of the first plan but before executing the second step of the first plan and, as a result of re-evaluating, selecting the second plan to continue executing the query;

executing a second step of the second plan to produce a result; and

storing the result.

8. The database system of claim 7 wherein selecting the first plan comprises:

selecting the one of the plurality of plans that has the least cost.

9. The database system of claim 7 wherein:

the second plan has a first step and a second step; and

executing the second plan comprises executing the first step of the second plan and the second step of the second plan.

10. The database system of claim 7, the process further comprising:

determining that the first plan is no longer viable; and

, in response selecting the second plan to execute the query.

11. The database system of claim 10 wherein determining that the first plan is no longer viable comprises:

determining that a system resource necessary to perform the first plan has malfunctioned.

12. The database system of claim 10 wherein determining that the first plan is no longer viable comprises:

determining that a system resource necessary to perform the first plan has become busy.

13. A computer program, stored in a non-transitory computer-readable tangible medium, for optimizing execution of a query, the program comprising executable instructions that cause a computer to:

evaluate a plurality of plans for executing a query on a multi-database system, the plurality of plans including a first plan and a second plan different from the first plan, and, as a result, select a first plan, the first plan having a first step and a second step, the second plan having a first step and a second step, the first step of the first plan being the same as the first step of the second plan, and the second step of the first plan being different from the second step of the second plan;

execute the first step of the first plan, after evaluating the plurality of plans and selecting the first plan;

re-evaluate the plan selection after executing the first step of the first plan but before executing the second step of the first plan and, as a result of, selecting a second plan to continue executing the query;

execute a second step of the second plan to produce a result; and

store the result.

14. The computer program of claim 13 wherein, when selecting the first plan, the computer:

selects the one of the plurality of plans that has the least cost.

15. The computer program of claim 13 wherein:

the second plan has a first step and a second step; and

executing the second plan comprises executing the first step of the second plan and the second step of the second plan.

16. The computer program of claim 13 further comprising executable instructions that cause the computer to:

determine that the first plan is no longer viable; and

, in response select the second plan to execute the query.

17. The computer program of claim 16 wherein, when determining that the first plan is no longer viable, the computer:

determines that a system resource necessary to perform the first plan has malfunctioned.

18. The computer program of claim 16 wherein, when determining that the first plan is no longer viable, the computer:

determines that a system resource necessary to perform the first plan has become busy.