All geek questions in one place

Oracle: why is parallel execution not used?

Look at the following query: If I comment on a subquery, it uses parallel execution, otherwise it is not.

After request

SELECT /*+ parallel(c, 20) */ 1, (SELECT 2 FROM DUAL) FROM DUAL c;
+2
source share
2 answers

You could find the answer in the documentation :

The SELECT statement can only be parallelized if the following conditions are met:

  • The request includes a parallel hint specification (PARALLEL or PARALLEL_INDEX) or the schema objects mentioned in the request have a Parallel declaration associated with them.

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

    • Full table scan

    • Multiple Section Index Range Scan

  • There are no scalar subqueries in the SELECT list.

Your query falls into the last barrier: it has a scalar subquery in its projection. If you want to parallelize a query, you need to find another way to write it.

+6
source

One idea might be to not use a subquery, but can you try and use a join? Your additional request seems quite simple, without grouping, etc. Therefore, you should not translate it into a connection.

Perhaps the optimizer cannot execute parallel execution when there are subqueries.

+1
source

Source: https://habr.com/ru/post/914758/

More articles:


All Articles