I have to say to all that this has been a really fun thread.... thanks to you all!!
RF Robert G. Freeman - Oracle8i OCP Oracle DBA Technical Lead CSX Midtier Database Administration The Cigarette Smoking Man: Anyone who can appease a man's conscience can take his freedom away from him. -----Original Message----- Sent: Sunday, March 17, 2002 5:13 AM To: Multiple recipients of list ORACLE-L 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). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Freeman, Robert 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).