Layyr,
I can't remember the details of what examples
I have tried so far, but it's certainly been entertaining
trying to map all the things that the optimizer will do.
Like Stefane, I really try to avoid fixing local problems
with init.ora parameters (especially hidden ones) because
of global side-effects, and I also prefer to avoid hints
simply because they might stop Oracle from finding an
even better path in the next release. However, I do think
that hints are a safe option - when used judiciously and
with full knowledge of the data - because stored execution
paths (outlines) depend on them so much.
So, in case you haven't spotted them yet in 9i, I wonder
if the rmain reason why the anti/semi join parameters
have disappeared is because the following 6 hints are
now published:
hash_aj
merge_aj
nl_aj
hash_sj
merge_sj
nl_sj
Jonathan Lewis
http://www.jlcomp.demon.co.uk
Next Seminar - UK, April 3rd - 5th
http://www.jlcomp.demon.co.uk/seminar.html
Host to The Co-Operative Oracle Users' FAQ
http://www.jlcomp.demon.co.uk/faq/ind_faq.html
Author of:
Practical Oracle 8i: Building Efficient Databases
-----Original Message-----
To: Multiple recipients of list ORACLE-L <[EMAIL PROTECTED]>
Date: 16 March 2002 18:55
|Things start to get *really* interesting with the way the
CBO
|can transform and choose access paths for NOT IN / NOT EXISTS and IN
/
|EXISTS. For example, a NOT EXISTS can now (9i) be transformed into a
HASH or
|SEMI anti-join. Don't think that was possible in earlier versions (or
at
|least I couldn't make it happen)
|
|This also has a downside in a way. For example, in 8i with
always_anti_join
|set to hash, if I *know* a correlated nested loops anti-join approach
is
|preferred, I can code a correlated NOT EXISTS and rely upon a nested
loops
|anti-join. On the other hand, if I *know* the criteria and data is
such that
|a hash anti-join is preferable for that query, I would code the query
using
|a NOT IN, and assuming the condition for a hash anti join are met, I
would
|get the hash anti join. I can't depend on that in 9i unless I set the
|"_always_anti_join" parameter. Hopefully the CBO will make the right
choices
|and I will not have to set it or worry about it.
|
|Larry G. Elkins
|
|
|--
|Please see the official ORACLE-L FAQ: http://www.orafaq.com
|--
|Author: Larry Elkins
| 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).
--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author: Jonathan Lewis
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).
