Parallelizing Query Optimization

• US 20130212085A1
• Filed: 02/09/2012
• Published: 08/15/2013
• Est. Priority Date: 02/09/2012
• Status: Abandoned Application

• Current Assignee: iAnywhere Solutions Incorporated (SAP SE)
• Original Assignee: iAnywhere Solutions Incorporated (SAP SE)
• Inventors: NICA, Anisoara, Charlesworth, Ian Lorne
• Application Number:
  US13/369,500
• Publication Number:
  US 20130212085A1
• Time in Patent Office: Days
• US Class Current: 707/718
• CPC Class Codes: G06F 16/24532 of parallel queries
• CPC Class Codes: G06F 16/24542 Plan optimisation

Abstract

A system, computer-implemented method, and computer-program product embodiments for generating an access plan. A query optimizer includes an enumeration method which enumerates a plurality of subsets of a query. Each subset in the query has a plurality of partitions. The partitions of each subset are enumerated into enumerated partitions using at least one thread. For each partition, physical access plans are generated, using at least one thread. Physical access plans are generated in parallel with other physical access plans of different partitions and with other enumerating partitions. The number of threads that perform the enumeration and the generation is dynamically adapted according to a pool of threads available during the enumeration of the partitions and the generation of physical access plans, and a complexity of the query. From the generated physical access plans, a final access plan for the query is determined by choosing the most efficient access plan.

Claims

1. A computer-implemented method for generating an access plan, comprising:

providing a plurality of subsets of a query, each subset including a plurality of partitions;

enumerating, using at least one thread, each partition of each subset;

generating, using at least one thread, at least one physical access plan for each enumerated partition in parallel, wherein a number of threads that perform the enumerating and the generating is dynamically adapted according to a pool of threads available during the enumerating and the generating and a complexity of the query; and

determining the access plan for the query from the at least one generated physical access plan.

2. The computer-implemented method of claim 1, further comprising:

generating a query hypergraph; and

determining each subset from the query hypergraph.

3. The computer-implemented method of claim 1, wherein the enumerating further comprises:

determining an enumerated subset in the plurality of subsets, the enumerated subset including the plurality of enumerated partitions; and

initializing generating of at least one physical access plan for the enumerated subset in parallel with enumerating a plurality of partitions in remaining subsets of the plurality of subsets, wherein the enumerating and the generating is performed using different threads when a plurality of threads in the thread pool are available.

4. The computer-implemented method of claim 1, further comprising:

completing enumerating of the plurality of partitions in each subset prior to generating at least one physical access plan for each enumerated partition.

5. The computer-implemented method of claim 1, wherein the generating further comprises:

generating, using a first thread, a first physical access plan for a first enumerated partition associated with a first subset;

generating, using a second thread, a second physical access plan for a second enumerated partition associated with a second subset in parallel with generating the first physical access plan when a plurality of threads in the thread pool are available, wherein the first subset is not the same as the second subset.

6. The computer-implemented method of claim 1, wherein the generating further comprises:

generating, using a first thread, a physical access plan using at least one enumerated partition wherein the at least one enumerated partition is a union of a first subset and a second subset and wherein the first subset and the second subset comprise a first set; and

enumerating, using a second thread, at least one partition of a second set, wherein the second set is not a subset of either the first subset or the second subset, and wherein generating the physical access plan of the first set is in parallel with enumerating the at least one partition of the second set, when a plurality of threads in the thread pool are available.

7. The computer-implemented method of claim 1, wherein the enumerating further comprises:

enumerating, using a first thread, a plurality of partitions associated with a first subset; and

enumerating, using a second thread, a plurality of partitions associated with a second subset in parallel with the first subset, wherein the first subset is disjoint from the second subset.

8. The computer-implemented method of claim 1, further comprising:

manipulating data in a database using the access plan determined for the query; and

transmitting a result of the manipulation to a recipient device.

9. A system for generating an access plan, comprising:

a query optimizer configured to;

provide a plurality of subsets of a query, each subset including a plurality of partitions;

initialize, using at least one thread, a plan enumeration phase, the plan enumeration phase configured to enumerate each partition of each subset;

initialize, using at least one thread, a plan generation phase, the plan generation phase configured to generate at least one physical access plan for each enumerated partition in parallel with the plan enumeration phase, wherein a number of threads that perform the plan enumeration phase and the plan generation phase is dynamically adapted according to a pool of threads available during the plan enumeration phase and the plan generation phase, and a complexity of the query; and

determine the access plan for the query from at least one generated physical access plan.

10. The system of claim 9, wherein the query optimizer is further configured to:

generate a query hypergraph; and

determine each subset from the query hypergraph.

11. The system of claim 9, wherein the plan enumeration phase is further configured to:

determine an enumerated subset in the plurality of subsets, the enumerated subset including the plurality of enumerated partitions; and

initialize the plan generation phase for an at least one physical access plan for the enumerated subset, wherein the plan enumeration phase is configured to enumerate a plurality of partitions in remaining subsets of the plurality of subsets, and wherein the plan enumeration phase and the plan generation phase is performed in parallel using different threads when a plurality of threads in the thread pool are available.

12. The system of claim 9, wherein the query optimizer is further configured to complete the plan enumeration phase for the plurality of partitions in each subset prior to initializing the plan generation phase;

and

wherein the plan generation phase is further configured to generate the at least one physical access plan for each enumerated partition.

13. The system of claim 9, wherein the plan generation phase is further configured to:

generate, using a first thread, a first physical access plan for a first enumerated partition associated with a first subset;

generate, using a first thread, a second physical access plan for a second enumerated partition associated with a second subset in parallel with the first physical access plan when a plurality of threads in the thread pool are available, wherein the first subset is not the same as the second subset.

14. The system of claim 9, wherein the query optimizer is further configured to:

cause the plan generation phase to generate, using a first thread, a physical access plan using at least one enumerated partition, wherein the at least one enumerated partition is a union of a first subset and a second subset, and wherein the first subset and the second subset comprise a first set; and

cause the plan enumeration phase to enumerate, using a first thread, at least one partition of a second set, wherein the second set is not a subset of either the first subset or the second subset, and wherein the plan generation phase generates the physical access plan of the first set and the plan enumeration phase enumerates at least one partition of the second set in parallel when a plurality of threads in the thread pool are available.

15. The system of claim 9, wherein the plan enumeration phase is further configured to:

enumerate, using a first thread, a plurality of partitions associated with a first subset; and

enumerate, using a second thread, a plurality of partitions associated with a second subset in parallel with the first subset, wherein the first subset is disjoint from the second subset, and when a plurality of threads in the thread pool are available.

16. The system of claim 9, further comprising an execution unit configured to:

manipulate data in a database using the access plan for the query, the access plan determined by the query optimizer;

generate a result of the manipulation; and

transmit the result to a recipient device.

17. A computer-readable medium, having instructions stored thereon, wherein the instructions cause a computing device to perform operations for generating an access plan, comprising:

providing a plurality of subsets of a query, each subset including a plurality of partitions;

enumerating, using at least one thread;

each partition of each subset;

generating, using at least one thread, at least one physical access plan for each enumerated partition in parallel, wherein a number of threads that perform the enumerating and the generation is dynamically adapted according to a pool of threads available during the enumerating and the generating and a complexity of the query; and

determining the access plan for the query from the at least one generated physical access plan.

18. The computer-readable medium of claim 17, wherein the enumerating further comprises:

determining an enumerated subset in the plurality of subsets, the enumerated subset including the plurality of enumerated partitions; and

initializing generating of at least one physical access plan for the enumerated subset in parallel with enumerating a plurality of partitions in remaining subsets of the plurality of subsets, wherein the enumerating and the generating is performed using different threads when a plurality of threads in the thread pool are available.

19. The computer-readable medium of claim 17, wherein the generating further comprises:

generating, using a first thread, a first physical access plan for a first enumerated partition associated with a first subset;

generating, using a second thread, a second physical access plan for a second enumerated partition associated with a second subset in parallel with generating the first physical access plan when a plurality of threads in the thread pool are available, wherein the first subset is not the same as the second subset.

20. The computer-readable medium of claim 17, wherein the generating further comprises:

generating, using a first thread, a physical access plan using at least one enumerated partition wherein the at least one enumerated partition is a union of a first subset and a second subset and wherein the first subset and the second subset comprise a first set; and

enumerating, using a second thread, at least one partition of a second set, wherein generating the physical access plan of the first set is in parallel with enumerating at least one partition of the second set, wherein the second set is not a subset of either the first subset or the second subset and when a plurality of threads in the thread pool are available.