Oracle: is there any logical reason not to use parallel execution with subqueries in the SELECT list?

Question

Is there any logical reason for Oracle not to use parallel execution with scalar subqueries in the SELECT list? Why it shouldn't use them?

A SELECT statement can be parallelized only if the following conditions are satisfied:

The query includes a parallel hint specification (PARALLEL or PARALLEL_INDEX) or the schema objects referred to in the query have a PARALLEL declaration associated with them.

At least one of the tables specified in the query requires one of the following:

A full table scan

An index range scan spanning multiple partitions

No scalar subqueries are in the SELECT list.

You state in your first sentence that there are subqueries in the `SELECT` list. But your last sentence says that there are no scalar subqueries in the SELECT list. How can you have subqueries in the `SELECT` list that are not scalar subqueries? And are you asking why choosing a non-parallel execution plan might be preferrable? Or why parallelism would not be possible? — Justin Cave, Mar 15 '12 at 21:45
@JustinCave - the OP is quoting the Oracle documentation I cited in this answer to their earlier question. http://stackoverflow.com/a/9724227/146325 Only they mucked up the formatting, and changed its meaning in the process. — APC, Mar 15 '12 at 22:11

score 4 · Answer 1 · edited May 23 '17 at 11:56

Every item in that list is wrong.

(At least for Oracle 11gR2, and probably10g as well. The list may be accurate for some obsolete versions of Oracle.)

I recommend using the official Oracle documentation whenever possible, but the parallel execution chapter is not very accurate.

And even when the manual isn't wrong, it is often misleading, because parallel execution is very complicated. If you go through all the documentation you'll find there are about 30 different variables that determine the degree of parallelism. If you ever see a short checklist of items, you should be very skeptical. Those checklists are usually just the most relevant items to consider in a very specific context.

Example:

SQL> --Create a table without any parallel settings
SQL> create table parallel_test(a number primary key, b number);

Table created.

SQL> --Create some test data
SQL> insert into parallel_test
  2  select level, level from dual connect by level <= 100000;

100000 rows created.

SQL> commit;

Commit complete.

SQL> --Force the session to run the query in parallel
SQL> alter session force parallel query;

Session altered.
SQL> --Generate explain plan
SQL> explain plan for
  2  select a
  3     ,(
  4             select a
  5             from parallel_test parallel_test2
  6             where parallel_test2.a = parallel_test.a
  7     )
  8  from parallel_test;

Explained.

SQL> select * from table(dbms_xplan.display);

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------
Plan hash value: 3823224058

---------------------------------------------------------------------------------------------------------------------
| Id  | Operation               | Name         | Rows  | Bytes | Cost (%CPU)| Time     |    TQ  |IN-OUT| PQ Distrib |
---------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT        |              |   116K|  1477K|     9   (0)| 00:00:01 |        |      |            |
|*  1 |  INDEX UNIQUE SCAN      | SYS_C0028894 |     1 |    13 |     1   (0)| 00:00:01 |        |      |            |
|   2 |  PX COORDINATOR         |              |       |       |            |          |        |      |            |
|   3 |   PX SEND QC (RANDOM)   | :TQ10000     |   116K|  1477K|     9   (0)| 00:00:01 |  Q1,00 | P->S | QC (RAND)  |
|   4 |    PX BLOCK ITERATOR    |              |   116K|  1477K|     9   (0)| 00:00:01 |  Q1,00 | PCWC |            |
|   5 |     INDEX FAST FULL SCAN| SYS_C0028894 |   116K|  1477K|     9   (0)| 00:00:01 |  Q1,00 | PCWP |            |
---------------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - access("PARALLEL_TEST2"."A"=:B1)

Note
-----
   - dynamic sampling used for this statement (level=2)

21 rows selected.

SQL>

No parallel hint, no parallel objects, no full table scans, no index range scans spanning multiple partitions, and a scalar subquery.

Not a single condition met, yet the query still uses parallelism. (I also verified v$px_process to make sure that the query really does use parallelism, and it's not just an explain plan failure.)

This means the answer to your other question is wrong.

I'm not sure exactly what's going on in that case, but I think it has to do with the FAST DUAL optimization. In some contexts, DUAL isn't used as a table, so there's nothing to parallelize. This is probably a "bug", but if you're using DUAL then you really don't want parallelism anyway. (Although I assume you used DUAL for demonstration purposes, and your real query is more complicated. If so, you may need to update the query with a more realistic example.)

Jon - the OP edited the text of their other question. Originally they posted their actual query - you can see a reference to one ofthe tables in my answer. The real query was way more complicated than its replacement, joining multiple tables in the FROM clause and the scalar subquery. I think the reason why you are seeing a parallel execution is that your scalar subquery is directly bound to the reference table. This was not the case with the OP's original statement. — APC, Mar 16 '12 at 09:57
I don't have access to Oracle right now, but I will rustle up a more realistic test over the weekend. — APC, Mar 16 '12 at 09:57
@APC I just ran a test with a different table in the scalar subquery, and it still runs in parallel. I'm sure there are cases where adding a scalar subquery will stop parallel execution, but only as a side affect of some other change. I'll take another look at the original query. (In general it's a great idea to reduce every issue to a query using `DUAL`, but this problem is an exception.) — Jon Heller, Mar 17 '12 at 02:47

Oracle: is there any logical reason not to use parallel execution with subqueries in the SELECT list?

1 Answers1

Linked