On Monday 28 May 2001 04:45, Nuno Souto wrote:
> And a few others. I found out the problem I reported
> a while ago with CBO suddenly going South on hash scan joins and
> completely ignoring nested loops or indexes is actually an introduced
> problem due to a change in CBO rules after 8.0.4. It first
> affected SAP users. It used to be fixed by a couple of events
> which due to their usefulness, became the two "new" optimizer cost
> adjust parameters in later versions of 8.0 and some of the 8.1.
>
> <groan... roll on 9i, I've had enough of this optimizer!>
Nuno, here's an interesting bit in the tuning manual that may
take care of the hash join problem.
"For a view with multiple base tables on the right side of an outer join, the
optimizer can push the join predicate into the view (see "Pushing the
Predicate into the View") if the initialization parameter
_PUSH_JOIN_PREDICATE is set to TRUE or the accessing query contains the
PUSH_PRED hint.
Pushing a join predicate is a cost-based transformation that can enable more
efficient access path and join methods, such as transforming hash joins into
nested loops joins, and full table scans to index scans. "
I haven't had a chance to try this, as I don't currently have access to a
database with real data in it, but this could be the way to prevent those
hash join problems when migrating to 8i from 7.x.
Jared
--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author: Jared Still
INET: [EMAIL PROTECTED]
Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051
San Diego, California -- Public Internet access / Mailing Lists
--------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from). You may
also send the HELP command for other information (like subscribing).
