Executing filter subqueries using a parallel single cursor model

• US 7,958,160 B2
• Filed: 05/06/2004
• Issued: 06/07/2011
• Est. Priority Date: 12/16/2003
• Status: Active Grant

• Current Assignee: Oracle International Corporation (Oracle Corporation)
• Original Assignee: Oracle International Corporation (Oracle Corporation)
• Inventors: Ghosh, Bhaskar, Cruanes, Thierry, Chen, Rushan, Shankar, Shrikanth
• Primary Examiner(s): Wong; Don
• Assistant Examiner(s): Hwa; Shyue Jiunn
• Application Number:
  US10/841,991
• Publication Number:
  US 20050131877A1
• Time in Patent Office: 2,588 Days
• Field of Search: 707/769, 707/999.003, 707/803
• US Class Current: 707/803
• CPC Class Codes: G06F 16/24532 of parallel queries

Abstract

Techniques are provided for generating execution plans for, and executing, database statements that contain filter subqueries. Upon receiving a database statement that contains a filter subquery, the database server builds an execution plan that includes a filter operation that is fed by a subtree that corresponds to the subquery. The database server performs a cost analysis to determine whether (1) the filter operation should be parallelized, and (2) the subquery tree should be parallelized. Techniques are also described for generating plans and executing queries where (1) both the filter operation and the subquery subtree are parallelized, (2) the filter operation is parallelized but the subquery subtree operation is performed serially, and (3) the filter operation is performed serially but the subquery subtree operation is parallelized.

Claims

1. A method comprising the steps of:

receiving, at a database server, a first query that includes (1) a first select statement and (2) a second query in a clause of the first query, wherein the second query includes a second select statement;

determining, at the database server, to parallelize first operation of a subquery operation or a filter operation, wherein the subquery operation is an operation required to execute the-second query, wherein the filter operation uses results produced by the subquery operation;

determining, at the database server, not to parallelize a second operation, wherein the second operation is the other operation of the subquery operation or the filter operation;

wherein the database server determines not to parallelize the second operation based, at least in part, on one of more of;

a cost associated with parallelizing the second operation, data that declares an object involved in the second operation to be serial, or semantics of a predicate associated with the second operation;

generating an execution plan that includes representations corresponding to both the subquery operation and the filter operation;

using a set of slave processes to access either the execution plan or an equivalent execution plan, wherein the set of slave processes participate in parallel execution of the first operation; and

executing the second operation in serial;

wherein the method is performed by one or more computing devices.

2. The method of claim 1 wherein:

the step of generating an execution plan includes generating an execution plan that indicates that said filter operation is to be parallelized; and

the method further comprises using a same set of slave processes to both (a) scan in parallel a database object that feeds said filter operation, and

(b) evaluate the filter operation in parallel.

3. The method of claim 2 wherein the step of generating an execution plan includes generating an execution plan that indicates that said subquery operation is to be parallelized, wherein the execution plan includes a first parallelizer row source associated with the subquery operation and a second parallelizer row source associated with the filter operation, wherein the first parallelizer row source is nested below the second parallelizer row source within the execution plan.

4. The method of claim 1 wherein:

the subquery operation involves generating a first set of rows; and

the filter operation involves executing probes into the first set of rows based on a second set of rows.

5. The method of claim 4 wherein:

a plurality of filter slaves is assigned to the filter operation;

the plurality of filter slaves perform filtering based on probes performed in the subquery operation; and

each subquery operation is executed serially in a slave process.

6. The method of claim 1 wherein:

the execution plan includes a first parallelizer row source associated with the subquery operation or the filter operation;

the execution plan includes a buffer sort row source below the first parallelizer row source; and

the method further includes the step of slaves executing the buffer source row source as a pass-through rowsource.

7. The method of claim 1 wherein:

the step of generating an execution plan includes generating an execution plan that indicates that said filter operation is to be parallelized and that said subquery operation is to be performed serially;

said set of slave processes perform the filter operation in parallel; and

each slave performs the subquery operation in serial.

8. The method of claim 1 wherein:

the step of generating an execution plan includes generating an execution plan that indicates that said subquery operation is to be parallelized and that said filter operation is to be performed serially;

said set of slave processes perform the subquery operation in parallel; and

a single process is used to perform the filter operation based on probes that are performed in parallel by slaves in said set of slave processes.

9. The method of claim 8 wherein:

the filter operation involves repeatedly performing the steps of;

the single process obtaining a row; and

using the set of slaves to perform a parallel probe based on the row.

10. The method of claim 1 wherein slaves within the set of slaves filter rows produced by the subquery operation based on a predicate that is associated with the filter operation.

11. A method comprising:

receiving, at a database server, a first query that includes (1) a first select statement and (2) a second query in a clause of the first query, wherein the second query includes a second select statement;

determining, at the database server, whether to parallelize at least one of a subquery operation or a filter operation, wherein the subquery operation is an operation required to execute the second query, wherein the filter operation uses results produced by the subquery operation;

in response to determining that at least one operation of the subquery operation or the filter operation is to be parallelized;

generating and storing, in volatile or non-volatile memory, an execution plan that includes representations corresponding to both the subquery operation and the filter operation, and

using a set of slave processes that are to participate in parallel execution of the at least one operation to access either the execution plan or an equivalent execution plan;

wherein the database server determines whether to parallelize the subquery operation before determining whether to parallelize the filter operation;

wherein the method is performed by one or more computing devices.

12. A method comprising:

receiving, at a database server, a first query that includes (1) a first select statement and (2) a second query in a clause of the first query, wherein the second query includes a second select statement;

determining, at the database server, whether to parallelize at least one of a subquery operation or a filter operation, wherein the subquery operation is an operation required to execute the second query, wherein the filter operation uses results produced by the subquery operation;

in response to determining that at least one operation of the subquery operation or the filter operation is to be parallelized;

generating and storing, in volatile or non-volatile memory, an execution plan that includes representations corresponding to both the subquery operation and the filter operation, and

using a set of slave processes that are to participate in parallel execution of the at least one operation to access either the execution plan or an equivalent execution plan;

wherein the database server determines whether to parallelize the filter operation based, at least in part, on serial costs of the subquery operation and parallel costs of the subquery operation;

wherein the method is performed by one or more computing devices.

13. One or more volatile or non-volatile machine-readable media storing instructions which, when executed by one or more processors, cause:

receiving, at a database server, a first query that includes (1) a first select statement and (2) a second query in a clause of the first query, wherein the second query includes a second select statement;

determining, at the database server, to parallelize a first operation of a subquery operation or a filter operation, wherein the subquery operation is an operation required to execute the second query, wherein the filter operation uses results produced by the subquery operation;

determining, at the database server, not to parallelize a second operation, wherein the second operation is the other operation of the subquery operation or the filter operation;

wherein the database server determines not to parallelize the second operation based, at least in part, on one of more of;

a cost associated with parallelizing the second operation, data that declares an object involved in the second operation to be serial, or semantics of a predicate associated with the second operation;

generating an execution plan that includes representations corresponding to both the subquery operation and the filter operation;

using a set of slave processes to access either the execution plan or an equivalent execution plan, wherein the set of slave processes participate in parallel execution of the first operation; and

executing the second operation in serial.

14. The volatile or non-volatile media of claim 13 wherein:

generating an execution plan includes generating an execution plan that indicates that said filter operation is to be parallelized; and

the instructions, when executed by the one or more processors, further causes using a same set of slave processes to both

(a) scan in parallel a database object that feeds said filter operation, and

(b) evaluate the filter operation in parallel.

15. The volatile or non-volatile media of claim 14 wherein generating an execution plan includes generating an execution plan that indicates that said subquery operation is to be parallelized, wherein the execution plan includes a first parallelizer row source associated with the subquery operation and a second parallelizer row source associated with the filter operation, wherein the first parallelizer row source is nested below the second parallelizer row source within the execution plan.

16. The volatile or non-volatile media of claim 13 wherein:

the subquery operation involves generating a first set of rows; and

the filter operation involves executing probes into the first set of rows based on a second set of rows.

17. The volatile or non-volatile media of claim 16 wherein:

a plurality of filter slaves is assigned to the filter operation;

the plurality of filter slaves perform filtering based on probes performed in the subquery operation; and

each subquery operation is executed serially in a slave process.

18. The volatile or non-volatile media of claim 13 wherein:

the execution plan includes a first parallelizer row source associated with the subquery operation or the filter operation;

the execution plan includes a buffer sort row source below the first parallelizer row source; and

the instructions, when executed by the one or more processors, further causes slaves executing the buffer source row source as a pass-through rowsource.

19. The volatile or non-volatile media of claim 13 wherein:

generating an execution plan includes generating an execution plan that indicates that said filter operation is to be parallelized and that said subquery operation is to be performed serially;

said set of slave processes perform the filter operation in parallel; and

each slave performs the subquery operation in serial.

20. The volatile or non-volatile media of claim 13 wherein:

generating an execution plan includes generating an execution plan that indicates that said subquery operation is to be parallelized and that said filter operation is to be performed serially;

said set of slave processes perform the subquery operation in parallel; and

a single process is used to perform the filter operation based on probes that are performed in parallel by slaves in said set of slave processes.

21. The volatile or non-volatile media of claim 20 wherein the filter operation involves repeatedly performing the steps of:

the single process obtaining a row; and

using the set of slaves to perform a parallel probe based on the row.

22. The volatile or non-volatile media of claim 13 wherein slaves within the set of slaves filter rows produced by the subquery operation based on a predicate that is associated with the filter operation.

23. One or more volatile or non-volatile machine-readable media storing instructions which, when executed by one or more processors, cause:

receiving, at a database server, a first query that includes (1) a first select statement and (2) a second query in a clause of the first query, wherein the second query includes a second select statement;

determining, at the database server, whether to parallelize at least one of a subquery operation or a filter operation, wherein the subquery operation is an operation required to execute the second query, wherein the filter operation uses results produced by the subquery operation;

in response to determining that at least one operation of the subquery operation or the filter operation is to be parallelized;

generating and storing, in volatile or non-volatile memory, an execution plan that includes representations corresponding to both the subquery operation and the filter operation, and

using a set of slave processes that are to participate in parallel execution of the at least one operation to access either the execution plan or an equivalent execution plan;

wherein the database server determines whether to parallelize the subquery operation before determining whether to parallelize the filter operation.

24. One or more volatile or non-volatile machine-readable media storing instructions which, when executed by one or more processors, cause:

receiving, at a database server, a first query that includes (1) a first select statement and (2) a second query in a clause of the first query, wherein the second query includes a second select statement;

determining, at the database server, whether to parallelize at least one of a subquery operation or a filter operation, wherein the subquery operation is an operation required to execute the second query, wherein the filter operation uses results produced by the subquery operation;

in response to determining that at least one operation of the subquery operation or the filter operation is to be parallelized;

generating and storing, in volatile or non-volatile memory, an execution plan that includes representations corresponding to both the subquery operation and the filter operation, and

using a set of slave processes that are to participate in parallel execution of the at least one operation to access either the execution plan or an equivalent execution plan;

wherein the database server determines whether to parallelize the filter operation based, at least in part, on serial costs of the subquery operation and parallel costs of the subquery operation.