Parallel hint ignored only in subquery

From: Rich Jesse <rjoralist_at_society.servebeer.com>
Date: Thu, 24 Jul 2008 11:36:03 -0500 (CDT)
Message-ID: <34908.12.17.117.251.1216917363.squirrel@12.17.117.251>


Hey all,

In 10.1.0.5.0 on AIX, I'm attempting to merge an app's "lookup" query with it's driving SQL, i.e. app currently runs nasty query, then runs a SELECT for each row returned.

The query has 12 tables joined, with 10 of the 11 joins being outer (LEFT OUTER JOIN in ANSI syntax, if that matters). From the explain plan, the driving table is obvious and I've successfully set a parallel hint on it's FTS for excellent runtime performance gains.

However, I now need to make this query into a subquery to use analytical functions (got the dreaded ORA-1445 using RANK). When I explain this new plan, the parallelism goes away and it takes 10x longer to return. I won't/can't dump the entire statement here, but it's something like:

SELECT * FROM (
SELECT /*+ parallel (aa 4) */
aa.col1, bb.col1, cc.col1, cc2.col1...
FROM aa
JOIN bb on aa.pk = bb.pk
LEFT OUTER JOIN cc on cc.pk1 = aa.pk AND cc.pk3 = aa.pk1 LEFT OUTER JOIN cc CC2 on cc2.pk2 = aa.pk2 ...
WHERE bb.stuff IN ('yadda', '?Yadda', 'YADDA!') );

Without the simple "SELECT * FROM (" wrapper, the PARALLEL hint works fine. I've tried adding an UNNEST hint in the inner and outer SELECTs as well as various PARALLEL hints to no avail.

Thoughts anyone?
Rich

--
http://www.freelists.org/webpage/oracle-l
Received on Thu Jul 24 2008 - 11:36:03 CDT

Original text of this message