Parallel execution of queries with a recursive clause
Techniques are described herein for executing queries with a recursive clause using a plurality of processes that execute database operations in parallel. Each process of the plurality of processes either generate or are assigned a segment that is part of a temporary table. For each iteration of the recursive query, work granules are divided up and assigned to each of the plurality of processes. As each respective process produces a portion of a result set for a given iteration, that process appends said portion of the result set to the respective segment that the respective process manages. Each slave process then publishes, to one or more sources, a reference to the newly generated results. During the next iteration, any slave process may access any of the data from the previous iteration.
1. A method comprising:
executing a query with a recursive clause wherein the executing includes:
generating a temporary table for a first set of results, wherein the temporary table comprises a plurality of segments, each segment of said plurality of segments being associated with a respective process of a plurality of processes that execute database operations in parallel;
indicating the first set of results are associated with a first iteration of the recursive clause; and
for one or more additional iterations, each corresponding process of the plurality of processes performing in parallel with one or more other processes of the plurality processes:
retrieving, from the temporary table, a set of results from a previous iteration based on an indication that associates the set of results with the previous iteration;
performing one or more operations on the set of results from the previous iteration thereby generating a respective additional set of results;
indicating the respective additional set of results is associated with a respective additional iteration;
appending the respective additional set of results to a corresponding segment associated with said each corresponding process; and
wherein said each corresponding process produced the respective additional set of results by performing, in parallel with the one or more other processes of the plurality processes, the one or more operations.
2. The method of claim 1 , wherein the plurality of processes are executing on a plurality of database servers; the method further comprising:
for a respective segment of the plurality of segments associated with the respective process of the plurality of processes,
storing, in a volatile memory of a database server that is executing the respective process, at least a portion of the respective segment associated with the respective process;
storing, in the volatile memory of the database server, one or more mappings to the respective segment.
3. The method of claim 1 , wherein the plurality of processes are executing on a plurality of database servers operatively coupled to a shared storage; the method further comprising:
for a respective segment of the plurality of segments associated with the respective process of the plurality of processes,
storing, in the shared storage, the respective segment associated with the respective process;
storing, in a volatile memory of the database server executing the respective process, one or more mappings to the respective segment; and
merging, mappings of the plurality of segments, into a single particular segment associated with the temporary table.
4. The method of claim 1 , further comprising performing, in parallel on the plurality of processes, a final set of one or more database operations against the temporary table generated based on the recursive clause of the query.
5. The method of claim 1 , wherein each process of the plurality of processes is locked from performing other database operations for other queries until completing execution of at least the recursive clause of the query.
6. The method of claim 1 , wherein the temporary table is internally defined and associated with a cursor; the method further comprising:
deleting the temporary table when the cursor ages out.
7. The method of claim 1 , further comprising:
checking for an end condition for the recursive clause after each iteration; and
if and only if the end condition is not found,
creating work granules for a next iteration; and
distributing the work granules to the plurality of processes to execute in parallel.
8. One or more non-transitory computer-readable media storing one or more sequences of instructions which, when executed by one or more processors, cause performing of:
executing a query with a recursive clause wherein the executing includes:
generating a temporary table for a first set of results, wherein the temporary table comprises a plurality of segments, each segment of said plurality of segments being associated with a respective process of a plurality of processes that execute database operations in parallel;
indicating the first set of results are associated with a first iteration of the recursive clause; and
for one or more additional iterations, each corresponding process of the plurality of processes performing in parallel with one or more other processes of the plurality processes:
retrieving, from the temporary table, a set of results from a previous iteration based on an indication that associates the set of results with the previous iteration;
performing one or more operations on the set of results from the previous iteration thereby generating a respective additional set of results;
indicating the respective additional set of results is associated with a respective additional iteration;
appending the respective additional set of results to a corresponding segment associated with said each corresponding process; and
wherein said each corresponding process produced the respective additional set of results by performing, in parallel with the one or more other processes of the plurality processes, the one or more operations.
9. The one or more non-transitory computer-readable media of claim 8 , wherein the plurality of processes are executing on a plurality of database servers; wherein the one or more non-transitory computer-readable media store instructions which, when executed by the one or more processors, further cause:
for a respective segment of the plurality of segments associated with the respective process of the plurality of processes,
storing, in a volatile memory of a database server that is executing the respective process, at least a portion of the respective segment associated with the respective process;
storing, in the volatile memory of the database server, one or more mappings to the respective segment.
10. The one or more non-transitory computer-readable media of claim 8 , wherein the plurality of processes are executing on a plurality of database servers operatively coupled to a shared storage; wherein the one or more non-transitory computer-readable media store instructions which, when executed by the one or more processors, further cause:
for a respective segment of the plurality of segments associated with the respective process of the plurality of processes,
storing, in the shared storage, the respective segment associated with the respective process;
storing, in a volatile memory of the database server executing the respective process, one or more mappings to the respective segment; and
merging, mappings of the plurality of segments, into a single particular segment associated with the temporary table.
11. The one or more non-transitory computer-readable media of claim 8 , storing instructions which, when executed by the one or more processors, further cause: performing, in parallel on the plurality of processes, a final set of one or more database operations against the temporary table generated based on the recursive clause of the query.
12. The one or more non-transitory computer-readable media of claim 8 , wherein each process of the plurality of processes is locked from performing other database operations for other queries until completing execution of at least the recursive clause of the query.
13. The one or more non-transitory computer-readable media of claim 8 , wherein the temporary table is internally defined and associated with a cursor; wherein the one or more non-transitory computer-readable media, store instructions which, when executed by the one or more processors, further cause:
deleting the temporary table when the cursor ages out.
14. The one or more non-transitory computer-readable media of claim 8 , storing instructions which, when executed by the one or more processors, further cause:
checking for an end condition for the recursive clause after each iteration; and
if and only if the end condition is not found,
creating work granules for a next iteration; and
distributing the work granules to the plurality of processes to execute in parallel.
15. A database management system comprising one or more computing devices configured to:
executing a query with a recursive clause wherein the executing includes:
generate a temporary table for a first set of results, wherein the temporary table comprises a plurality of segments, each segment of said plurality of segments being associated with a respective process of a plurality of processes that execute database operations in parallel;
indicate the first set of results are associated with a first iteration of the recursive clause; and
for one or more additional iterations, each corresponding process of the plurality of processes perform in parallel with one or more other processes of the plurality processes:
retrieving, from the temporary table, a set of results from a previous iteration based on an indication that associates the set of results with the previous iteration;
performing one or more operations on the set of results from the previous iteration thereby generating a respective additional set of results;
indicating the respective additional set of results is associated with a respective additional iteration;
appending the respective additional set of results to a corresponding segment associated with said each corresponding process; and
wherein said each corresponding process produced the respective additional set of results by performing, in parallel with the one or more other processes of the plurality processes, the one or more operations.
16. The database management system of claim 15 , wherein the one or more computer devices are a plurality of database servers, wherein the plurality of processes are executing on the plurality of database servers; wherein the one or more computing devices are further configured to:
for a respective segment of the plurality of segments associated with the respective process of the plurality of processes,
store, in a volatile memory of a database server that is executing the respective process, at least a portion of the respective segment associated with the respective process;
store, in the volatile memory of the database server, one or more mappings to the respective segment.
17. The database management system of claim 15 , wherein the one or more computing devices are a plurality of database servers, where in the plurality of processes are executing on the plurality of database servers operatively coupled to a shared storage; wherein the one or more computing devices are further configured to:
for a respective segment of the plurality of segments associated with the respective process of the plurality of processes,
store, in the shared storage, the respective segment associated with the respective process;
store, in a volatile memory of the database server executing the respective process, one or more mappings to the respective segment; and
merge, mappings of the plurality of segments, into a single particular segment associated with the temporary table.
18. The database management system of claim 15 , wherein the one or more computing devices are further configured to: perform, in parallel on the plurality of processes, a final set of one or more database operations against the temporary table generated based on the recursive clause of the query.
19. The database management system of claim 15 , wherein each process of the plurality of processes is locked from performing other database operations for other queries until completing execution of at least the recursive clause of the query.
20. The database management system of claim 15 , wherein the one or more computing devices are further configured to:
check for an end condition for the recursive clause after each iteration; and
if and only if the end condition is not found,
create work granules for a next iteration; and
distribute the work granules to the plurality of processes to execute in parallel.
21. The method of claim 1 , wherein retrieving, from the temporary table, the set of results from the previous iteration based on the indication that associates the set of results with the previous iteration, is performed by:
each process of the plurality of processes:
generating one or more work granules to perform scanning against a portion of the temporary table;
performing said scanning against the portion of the temporary table defined by the one or more work granules; and
based on the scanning, distributing, to the plurality of processes, the set of results from the previous iteration.
22. The one or more non-transitory computer-readable media of claim 8 , storing instructions which, when executed by the one or more processors, further cause:
each process of the plurality of processes:
generating one or more work granules to perform scanning against a portion of the temporary table;
performing said scanning against the portion of the temporary table defined by the one or more work granules; and
based on the scanning, distributing, to the plurality of processes, the set of results from the previous iteration.