Hi Team,
I have a query that looks something like this
SELECT *
FROM
X
INNER JOIN
(SELECT * FROM B WHERE ....) Y ON X.key=Y.key
INNER JOIN
(SELECT * FROM C WHERE ....) Z ON X.key=Z.key;
To optimize this, I added the parallel hint on the largest of the 3 tables in the join
SELECT /*+parallel(X,8)*/ *
FROM
X
INNER JOIN
(SELECT * FROM B WHERE ....) Y ON X.key=Y.key
INNER JOIN
(SELECT * FROM C WHERE ....) Z ON X.key=Z.key;
There was a marginal improvement in the performance but when I used the hint with only the DOP and not the table name, the query executed almost 10 times faster.
SELECT /*+parallel(8)*/ *
FROM
X
INNER JOIN
(SELECT * FROM B WHERE ....) Y ON X.key=Y.key
INNER JOIN
(SELECT * FROM C WHERE ....) Z ON X.key=Z.key;
Can you help me understand what is the difference between the 2 approaches?