Parallel hint ignored only in subquery
- From: "Rich Jesse" <rjoralist@xxxxxxxxxxxxxxxxxxxxx>
- To: oracle-l@xxxxxxxxxxxxx
- Date: Thu, 24 Jul 2008 11:36:03 -0500 (CDT)
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
--
//www.freelists.org/webpage/oracle-l
Other related posts:
