The beach part sounds great, though I don't care to see naked men running around ;-) But it still beats doing this techie thing all the time.
FWIW, a NOT IN *can* use an index under the CBO, and has done so since early 7, behaving very much like a correlated NOT EXISTS with a slight difference for nulls handling. Then in 7.3.2 when you could hint a hash_aj, or, set always_anti_join to hash (or merge), it could drift away from a correlated approach, using a hash or merge anti-join, *if* that was the appropriate approach. Maybe using an FTS, or an index fast full scan, or an index full scan. And whether to correlate or not depends on the data and the query, it's criteria, data, etc. And the MINUS operator is always an option that works well in some cases. The "outer join" and "key is null" trick that Stephane and Jared showed can be *very* useful. I can't remember where I first saw that trick, but I know Guy Harrison's SQL Tuning book, first edition, discusses it. I had seen it prior to that, but Harrison's book is at least one reference to the technique that I know of. And there are quite a few people who use the trick. It is especially useful when you *don't* want a correlated approach but the requirement for being able to use a hash-aj can't be met -- I had to use it on Thursday. Dropped that baby from 38 minutes to under a minute using the outer join and key is null trick along with a hash join outer approach. 9i is interesting because while the NOT EXISTS uses the correlated approach in 8i and earlier, which, depending upon the case, may or may not be a killer on performance, 9i can un-correlate it and use a hash-aj (or merge-aj), *if appropriate*. This isn't possible in 8i or below. So, it can very well take both a NOT EXISTS and NOT IN and make them hash-aj's. I hope the CBO makes the right decision for us ;-) Right now on a DW and a few DM's I deal with on 8.1.7, always_anti_join is set to hash -- that way if I want a correlated approach I use the NOT EXISTS. If the correlated approach is *not* the best approach, I can use a NOT IN and let the hash-aj come into play. Ok, off to the travel web sites, you folks got me thinking about beaches, islands, and water. Oh well, here is an example of a NOT EXISTS getting the hash-aj treatment under 9i: SQL> select * 2 from code_master 3 where not exists (select null 4 from code_detail 5 where code_master.code = code_detail.code) 6 / Execution Plan ---------------------------------------------------------- 0 SELECT STATEMENT Optimizer=CHOOSE (Cost=770 Card=100 Bytes=1500) 1 0 HASH JOIN (ANTI) (Cost=770 Card=100 Bytes=1500) 2 1 TABLE ACCESS (FULL) OF 'CODE_MASTER' (Cost=77 Card=100000 Bytes=1100000) 3 1 INDEX (FAST FULL SCAN) OF 'CD_CODE_IDX' (NON-UNIQUE) (Cost=208 Card=299600 Bytes=1198400) Regards, Larry G. Elkins [EMAIL PROTECTED] 214.954.1781 > -----Original Message----- > From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]]On Behalf Of > [EMAIL PROTECTED] > Sent: Friday, May 17, 2002 5:53 PM > To: Multiple recipients of list ORACLE-L > Subject: Re: Do you ever have days where you dont want to think ? > > > Yeah, I have those days. > > The beach sounds good; don't worry, I'll go find my own. > > Wouldn't want to frighten the natives. > > Try 'not exists', 'not in' forces a table scan. > > Even better, use an explicit anti-join. > > select a.f1, a.f2, a.f3, a.f4 > from table1 a, table2 b > where a.f1 = b.n1(+) > and b.n1 is null; > > I learned this from Larry Elkins, I *think* I did it properly. > > Rather clever I thought. > > Jared -- 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).
