sql - 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.

Answer 1

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.)