Join order restrictions

Patent metadata

Field	Value
Publication number	US-9934280-B2
Application number	US-201213469641-A
Country	US
Kind code	B2
Filing date	May 11, 2012
Priority date	May 13, 2011
Publication date	Apr 3, 2018
Grant date	Apr 3, 2018

Abstract

A query that is submitted to a database is analyzed so as to determine a join order restriction. The join order restriction is associated with a join between two tables of a plurality of tables of the database that participate in the query. The join order restriction constrains its associated join to be executed prior to another join of the query. At least one join plan is generated, the join plan being constrained by the join order restriction. Different join plans include different join orders. A join plan is selected from among the join plans for execution of the query.

First claim

We claim:

1. A method comprising:
analyzing, by a processor of a computing device, a query submitted to a database to determine a join order restriction that is associated with a join between two tables of a plurality of tables of the database that participate in the query, the join order restriction constraining its associated join to be executed prior to another join of the query, wherein analyzing the query to determine the join order restriction comprises:
determining a join order that produces a different result than a result obtained by executing the query according to a join order semantically expressed in the query; and
constructing the join order restriction to exclude the determined join order that produces the different result, from join plans for executing the query;

assigning a ranking of priority to each join between tables that participate in the query based on the join order restriction, including assigning a join associated with the join order restriction a lower priority than other joins in the query, wherein a join that was assigned a higher priority is executed prior to a join that was assigned a lower priority;
generating the join plans for executing the query constrained by the join order restriction, the join plans including different join orders;
selecting a join plan from the join plans for execution of the query; and
executing, by the processor, the query based on the selected join plan.

2. The method of claim 1 , wherein selecting the join plan for execution of the query comprises evaluating an optimization criterion for each join plan of the join plans.

3. The method of claim 2 , wherein the optimization criterion is related to an estimated execution time of the query that is executed in accordance with the join plan or to an estimated quantity of a resource that is utilized by the query that is executed in accordance with the join plan.

4. The method of claim 2 , wherein the optimization criterion comprises a cost value assigned to each join plan, and wherein the selected join plan is the join plan whose assigned cost value is smallest.

5. The method of claim 1 , further comprising:
representing the join order restriction by a plurality of ranks assigned to edges of a graph such that a join associated with a higher rank of the plurality of ranks is to be performed before a join associated with a relatively lower rank of the plurality of ranks.

6. A non-transitory computer readable storage medium storing instructions that when executed by a processor of a computing device cause the processor to:
obtain a query that is submitted to a database, the query expressible as including a plurality of joins, each join joining two tables of a plurality of tables of the database that participate in the query;
analyze the query to determine a join order restriction that is associated with a join of said plurality of joins, the join order restriction constraining its associated join to be executed prior to another join of said plurality of joins, wherein to analyze the query to determine the join order restriction, the processor is to determine a join order that produces a different result than a result obtained by executing the query according to a join order semantically expressed in the query, and construct the join order restriction to exclude the determined join order from join plans for executing the query;
assign priority rankings to the plurality of joins in the query, wherein to assign the priority rankings to the plurality of joins in the query, the instructions are to cause the processor to assign the join associated with the join order restriction a lower priority ranking than other joins in the query;
generate the join plans constrained by the join order restriction and the priority rankings;
select a join plan from the join plans for execution of the query; and
execute the query based on the selected join plan.

7. The non-transitory computer readable storage medium of claim 6 , wherein a join that was assigned a higher priority ranking is executed prior to a join that was assigned a lower priority ranking.

8. The non-transitory computer readable storage medium of claim 7 , wherein the join plans are generated when two of said plurality of joins are assigned equal priorities, such that those joins with the equal assigned priorities are executed in a different order in the join plans.

9. The non-transitory computer readable storage medium of claim 6 , wherein to select the join plan, the instructions are to cause the processor to evaluate comprises evaluation of an optimization criterion for each join plan of the join plans.

10. The non-transitory computer readable storage medium of claim 9 , wherein the optimization criterion for each join plan is related to an estimated execution time of the query in accordance with the join plan or to an estimated quantity of a resource that is utilized by the query that is executed in accordance with the join plan.

11. The non-transitory computer readable storage medium of claim 9 , wherein the evaluated optimization criterion for each join plan comprises a cost value that is assigned to that join plan and wherein the selected join plan is a join plan whose assigned cost value is smallest.

12. A system comprising:
a processing unit; and
a non-transitory computer readable medium storing a set of instructions that when executed by the processing unit cause the processing unit to:
obtain a query submitted to a database;
express the query as a plurality of joins, each join joining two tables of a plurality of tables of the database that participate in the query;
analyze the query to determine a join order restriction associated with a join of said plurality of joins, the join order restriction constraining its associated join to be executed prior to another join of said plurality of joins, wherein to determine the join order restriction, the processing unit is to determine a join order that produces a different result than a result obtained by executing the query according to a join order semantically expressed in the query, and construct the join order restriction to exclude the determined join order from join plans for execution of the query;
assign a priority ranking to each join of said plurality of joins of the query, wherein to assign a priority ranking to each join, the set of instructions is to cause the processing unit to assign the join associated with the join order restriction a lower priority ranking than other joins in the query;
generate the join plans including different join orders constrained by the join order constraint and the priority rankings;
select a join plan from the join plans based on an optimization criterion; and
execute the query based on the selected join plan.

13. The system of claim 12 , wherein a first join of the plurality of joins assigned with a higher priority ranking is to be performed prior to a second join of the plurality of joins assigned with a relatively lower priority ranking.

14. The system of claim 12 , wherein the set of instructions when executed by the processing unit cause the processing unit to represent the join order restriction by a plurality of ranks assigned to edges of a graph such that a join associated with a higher rank of the plurality of ranks is to be performed before a join associated with a relatively lower rank of the plurality of ranks.

15. The system of claim 12 , wherein the optimization criterion comprises an estimated execution time of the query in accordance with each join plan of the join plans.

16. The system of claim 12 , wherein the optimiz

Assignees

• Fuller Matthew Steven
• Lamb Andrew Allinson
• Shrinivas Lakshmikant
• Entit Software Llc

Inventors

• Fuller Matthew Steven
• Lamb Andrew Allinson
• Shrinivas Lakshmikant

Classifications

• G06F16/2456 Primary

  Join operations · CPC title

• G06F17/30498 Primary

  Physics · mapped topic