Parallel execution of queries with a recursive clause

• US 10,678,792 B2
• Filed: 09/16/2016
• Issued: 06/09/2020
• Est. Priority Date: 10/23/2015
• Status: Active Grant

• Current Assignee: Oracle International Corporation (Oracle Corporation)
• Original Assignee: Oracle International Corporation (Oracle Corporation)
• Inventors: Ziauddin, Mohamed, Kim, You Jung, Zhu, Yali, Witkowski, Andrew
• Primary Examiner(s): Boccio, Vincent F
• Application Number:
  US15/268,528
• Publication Number:
  US 20170116276A1
• Time in Patent Office: 1,362 Days
• Field of Search: 707773
• CPC Class Codes: G06F 16/24532 of parallel queries
• CPC Class Codes: G06F 16/24539 using cached or materialised query results
• CPC Class Codes: G06F 16/2454 Optimisation of common expressions
• CPC Class Codes: G06F 16/24547 Optimisations to support specific applications; Extensibility of optimisers
• CPC Class Codes: G06F 16/24561 Intermediate data storage techniques for performance improvement
• CPC Class Codes: G06F 16/24566 Recursive queries

Abstract

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.

Claims

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.

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.

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.

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.

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.