Fast algorithms for computing semijoin reduction sequences

• US 8,271,478 B2
• Filed: 07/27/2006
• Issued: 09/18/2012
• Est. Priority Date: 07/27/2006
• Status: Active Grant

• Current Assignee: SAP SE
• Original Assignee: SAP AG (SAP SE)
• Inventors: Hill, Gerhard L., Peh, Thomas
• Primary Examiner(s): BURKE, JEFF A
• Application Number:
  US11/494,065
• Publication Number:
  US 20080027904A1
• Time in Patent Office: 2,245 Days
• Field of Search: 707/2, 707769-770, 707/714, 707718-719
• US Class Current: 707/714
• CPC Class Codes: G06F 16/24534 Query rewriting; Transformation

Abstract

Systems and methods for using optimization techniques to construct a nearly optimal execution plan for an outer join query are disclosed. A query graph of the outer join query is constructed, by computing for each node the set of nodes influencing it, for each target node the set of its needed reducers, and predetermining the effects of all admissible moves in all possible sequences. The directed graph of all admissible move sequences is formed. An optimization process includes dynamically generating good estimations for the target distance of a search state. Some heuristics are disclosed for providing start solutions for the optimization process.

Claims

1. A method for executing an outer join query on data arranged in a plurality of databases in which all joins are evaluated by semijoins, the method comprising:

generating, by one or more programmable processors executing one or more computer programs, a query graph of nodes maintaining the data arranged in the plurality of databases and target nodes for an outer join query, comprising;

computing, by the one or more programmable processors executing one or more computer programs, for each node the set of nodes influencing it;

computing, by the one or more programmable processors executing one or more computer programs, for each target node the set of its needed reducers;

predetermining, by the one or more programmable processors executing one or more computer programs, the effects of admissible move sequences;

forming, by the one or more programmable processors executing one or more computer programs, a directed graph for the admissible move sequences; and

performing by the one or more programmable processors executing one or more computer programs, an optimization process using a search algorithm on the directed graph to determine a least-cost execution plan for the query, wherein the optimization process further comprises determining a start state, from a plurality of states in the formed directed graph, from which the determined execution plan is to start by performing one or more start heuristics procedures, the one or more start heuristics procedures comprising one or more first and second order heuristics, the least-cost execution plan for the query reducing total communication costs with regard to the query;

wherein a snake heuristic (i) determines costs for a next move continuing in a same direction along the query graph as compared to costs for the next move turning and moving behind a last turning point; and

(ii) selects the next move with lesser costs.

2. A method in accordance with claim 1, further comprising using, by the one or more programmable processors executing one or more computer programs, a close lower bound estimation of the future cost h(s) of a search state s, valid for the outer join query.

3. A method in accordance with claim 1, further comprising executing by the one or more programmable processors executing one or more computer programs, the execution plan to execute the outer join query.

4. A method in accordance with claim 1, where the use of heuristics and optimization, with regard to sequence and time, by the one or more programmable processors executing one or more computer programs, is subject to a time management scheme.

5. A method in accordance with claim 1, wherein the optimization process includes an A* optimization process, and includes maintaining, by the one or more programmable processors executing one or more computer programs, at least two lists of search states including an OPEN list having search states whose successors have not yet been constructed, and a CLOSED list having those search states whose successors have been constructed.

6. A method in accordance with claim 1, further comprising, wherein if the least cost of the search states in the OPEN list are substantially the same, ordering, by the one or more programmable processors executing one or more computer programs, the substantially identical search states in the OPEN list according to progress.

7. A method as in claim 1, wherein the query graph is a chain that does not have any branching, and wherein the snake heuristic operates such that:

after a move has been made on the query graph by the query, but a corresponding target node has not yet been fully reduced, a determination is made whether to continue moving in a same direction or to reverse the direction;

when a next move on the query graph leads to a node on an end of the chain, the query moves turns and moves to directly behind a last turning point; and

when there is a choice of next node in the query graph, costs for continuing in the same direction and turning and moving are determined and a move with lesser costs is selected.

8. A method for executing an outer join query on data arranged in a plurality of databases in which all joins are evaluated by semijoins, the method comprising:

generating by one or more programmable processors executing one or more computer programs, a query graph of nodes maintaining the data arranged in the plurality of databases and target nodes for the outer join query; and

performing, by the one or more programmable processors executing one or more computer programs, an optimization process, using a search algorithm on a directed graph to determine a least-cost execution plan for the query, wherein the optimization process further comprises determining a start state, from a plurality of states in the directed graph, from which the determined execution plan is to start by performing one or more start heuristics procedures, the one or more start heuristics procedures comprising one or more first and second order heuristics;

wherein a snake heuristic (i) determines costs for a next move continuing in a same direction along the query graph as compared to costs for the next move turning and moving behind a last turning point; and

(ii) selects the next move with lesser costs.

9. A method in accordance with claim 8, wherein generating a query graph further comprises:

computing, by the one or more programmable processors executing one or more computer programs, for each node the set of nodes influencing it; and

computing, by the one or more programmable processors executing one or more computer programs, for each target node the set of its needed reducers.

10. A method in accordance with claim 8, further comprising using, by the one or more programmable processors executing one or more computer programs, a close lower bound estimation of the future cost h(s) of a search state s, valid for the outer join query.

11. A method in accordance with claim 8, further comprising executing, by the one or more programmable processors executing one or more computer programs, the execution plan to execute the outer join query.

12. A method in accordance with claim 8, where the use of heuristics and optimization, with regard to sequence and time, by the one or more programmable processors executing one or more computer programs, is subject to a time management scheme.

13. A method in accordance with claim 8, wherein the optimization process includes an A* optimization process, and includes maintaining, by the one or more programmable processors executing one or more computer programs, at least two lists of search states including an OPEN list having search states whose successors have not yet been constructed, and a CLOSED list having those search states whose successors have been constructed.

14. A method in accordance with claim 13, further comprising, wherein if the least cost of the search states in the OPEN list are substantially the same, ordering, by the one or more programmable processors executing one or more computer programs, the substantially identical search states in the OPEN list according to progress.

15. A method as in claim 8, wherein the query graph is a chain that does not have any branching, and wherein the snake heuristic operates such that:

after a move has been made on the query graph by the query, but a corresponding target node has not yet been fully reduced, a determination is made whether to continue moving in a same direction or to reverse the direction;

when a next move on the query graph leads to a node on an end of the chain, the query moves turns and moves to directly behind a last turning point; and

when there is a choice of next node in the query graph, costs for continuing in the same direction and turning and moving are determined and a move with lesser costs is selected.

16. A computer program product embodied on a non-transitory storage media comprising, executable code to cause a data processing apparatus to perform operations comprising:

executing an outer join query on data arranged in a plurality of databases in which all joins are evaluated by semijoins in order to reduce a tuple list, the outer join query comprising;

generating a query graph of nodes maintaining the data arranged in the plurality of databases and target nodes for a query, comprising;

computing for each node the set of nodes influencing it;

computing for each target node the set of its needed reducers;

predetermining the effects of admissible move sequences;

forming a directed graph for the admissible move sequences; and

performing an optimization process, using a search algorithm on the directed graph to determine a least-cost execution plan for the query, wherein the optimization process further comprises determining a start state, from a plurality of states in the formed directed graph, from which the determined execution plan is to start by performing one or more start heuristics procedures, the one or more start heuristics procedures comprising one or more first and second order heuristic;

wherein a snake heuristic (i) determines costs for a next move continuing in a same direction along the query graph as compared to costs for the next move turning and moving behind a last turning point; and

(ii) selects the next move with lesser costs.

17. A computer program product as in claim 16, wherein the query graph is a chain that does not have any branching, and wherein the snake heuristic operates such that:

after a move has been made on the query graph by the query, but a corresponding target node has not yet been fully reduced, a determination is made whether to continue moving in a same direction or to reverse the direction;

when a next move on the query graph leads to a node on an end of the chain, the query moves turns and moves to directly behind a last turning point; and

when there is a choice of next node in the query graph, costs for continuing in the same direction and turning and moving are determined and a move with lesser costs is selected.