Parallel Hint In Select Query Without Table Name
Posted: Fri Jul 18, 2014 2:01 am
Data Experts!
This is not a DataStage question. It's in fact a query that needs tuning. It sits in one of the views that DataStage reads from and it's driving me crazy!
I have a query that looks something like this
To optimize this, I added the parallel hint on the largest of the 3 tables in the join
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.
Can you help me understand what is the difference between the 2 approaches? Is it an accepted approach that can be deployed in production?
This is not a DataStage question. It's in fact a query that needs tuning. It sits in one of the views that DataStage reads from and it's driving me crazy!
I have a query that looks something like this
Code: Select all
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;
Code: Select all
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;
Code: Select all
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;