Oracle can I use parallel hint in subquery?

Question

I have many complex select statements to run, which will take at least 2 hours, and I want to speed up the query. Can I use parallel hint both in subquery and mainquery to improve performance? For example, is the following valid?

select /*+ parallel(32) */
    a.id,
    (select /*+ parallel(32) */ b.col1 from table b where b.id = a.id)
from 
(select /*+ parallel(32) */ c.id, c.col2 
 from table c) a

I am new to parallel hints. Thank you in advance for your advice.

Answer 1

That subquery returns a single row. You probably do not want that run in parallel, but you will probably find that using a conventional join allows better parallelism for the query.

select /*+ parallel(32) */
       c.id,
       b.col1
from   table c left join
       table b on b.id = c.id

Bear in mind that your maximum effective degree of parallelism is likely to be limited by your i/o bandwidth in this case, not by the usual "2 x cores" that is commonly used.