I have the below query :

select /*+parallel(4)*/ ---> First Location
       <Desc_1> Amount,
       <Desc_2> Amount,
       <Desc_3> Amount
from (select /*+parallel(4)*/ ---> Second Location
      from <Example_Table>
      where <Conditions>
pivot(sum(Column_1) Amount
for <Column_2> in (1 as <Desc_1>,
                   2 as <Desc_2>,
                   3 as <Desc_3>)

The point is that <Example_Table> is a very big table and I want to ask where is the best to location to use the /*+parallel(4)*/ ? Is it better to use it in theSubQuery or in the outer select? What if we use /*+parallel(4)*/ in both outer and inner select? Is it bad for performance?

  • Does anyone have an opinion on this?
    – Pantea
    Jul 1 '20 at 6:10

Well, if you have just one table, and your outer select is not accessing other tables, I would guess that "Second Location" would be the only one the optimizer looks at. In practice, easy to test: just do an explain plan on the two options, and see. Then invoke it both ways, and verify what happens.

Oracle is pretty smart in doing the right thing for parallel queries; take a look at the following parameters, if neither option is producing a parallel execution plan:

select name, value from v$parameter where name like 'parallel%' order by 1;

And look at Oracle Database Reference section on "Initialization Parameters" such as parallel_adaptive_multi_user for details.

So nothing terrible is likely to happen if you put it in both places, but definately test in a development environment first.

  • Thanks for your answer. When I run your query , It gives me the error : Table or view does not exist. Does this mean that I do not have access to the table or view?
    – Pantea
    Aug 1 '20 at 14:49
  • 1
    More than likely. Double-check that you provided the owner of the table and spelling, etc. Oracle gives that error message whether the table does not exist, or you don't have access. Aug 2 '20 at 21:29

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.