2

Look at the following query: If I comment the subquery it uses parallel execution otherwise it doesn't.

After the query has been 

SELECT  /*+ parallel(c, 20) */
      1, (SELECT 2 FROM DUAL)
  FROM DUAL c;
Revious
  • 7,816
  • 31
  • 98
  • 147

2 Answers2

6

You could have found the answer in the documentation:

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.

Your query falls at the final hurdle: it has a scalar subquery in its projection. If you want to parallelize the query you need to find another way to write it.

APC
  • 144,005
  • 19
  • 170
  • 281
  • Thanks! I've never realized this problem. Why Oracle doesn't use parallel with subqueries in the select list? Is there any logical reason? – Revious Mar 15 '12 at 16:54
  • @APC I think the manual is wrong here. See my answer to the related question: http://stackoverflow.com/a/9731552/409172 – Jon Heller Mar 16 '12 at 03:48
1

One Idea could be not to use a subquery, but you can try and use a join? Your sub query seems fairly simply, no grouping etc, so it should not be an issue to translate it into a join.

Maybe the optimizer is not capable of parallel execution when there are subqueries.

ntziolis
  • 10,091
  • 1
  • 34
  • 50