Database Development

The difference between using parallelism “/*+parallel(4)*/” in the subquery or the outer query in terms of performance in Oracle

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?

Leave a Reply

Your email address will not be published. Required fields are marked *