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;
Add a comment
|
2 Answers
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.
2 Comments
Revious
Thanks! I've never realized this problem. Why Oracle doesn't use parallel with subqueries in the select list? Is there any logical reason?
Jon Heller
@APC I think the manual is wrong here. See my answer to the related question: stackoverflow.com/a/9731552/409172
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.
Comments
