Parallel Hint In Select Query Without Table Name

[jerome_rajan]

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

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;

To optimize this, I added the parallel hint on the largest of the 3 tables in the join

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;

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.

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;

Can you help me understand what is the difference between the 2 approaches? Is it an accepted approach that can be deployed in production?

[chulett]

So... Oracle, yes? Best way to see the why of this is via the Explain Plan to see the path the optimizer has decided to take through the tables / data in each case.

Ah... just saw the 'O' word in the additional info line.

[chulett]

What I believe that you should see is the parallel hint without the required 'tablespec' (rather than applying to all tables) is invalid and invalid hints become comments, so no error is thrown. Regardless, the explain plans should reveal all as there can be several factors at work here. Also realize that it is basically just a request for 8-way parallel and the database will silently use less or none at all if it doesn't think it has enough steam in the boiler at the time the SQL is launched.