Re: Fav. Urban Legend...

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 <ORACLE-L_at_fatcity.com> 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: elkinsl_at_flash.net
|
|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: ListGuru_at_fatcity.com (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: jonathan_at_jlcomp.demon.co.uk

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: ListGuru_at_fatcity.com (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).