RE: NOT EXISTS and NOT IN (was RE: SQL Help finding missing records)

Thanks for the clarification, Charles....

Eric

-----Original Message-----
From: rpg400-l-bounces@xxxxxxxxxxxx
[mailto:rpg400-l-bounces@xxxxxxxxxxxx]On Behalf Of Wilt, Charles
Sent: Thursday, July 17, 2008 9:20 AM
To: RPG programming on the AS400 / iSeries
Subject: NOT EXISTS and NOT IN (was RE: SQL Help finding missing
records)


Sorry guys, but OS/400 is smart enough to use an index even with a NOT IN.

A test on v5r2....

SELECT SPLVL, count(*)
FROM cstspnp
group by splvl

Salesperson Level COUNT ( * )
413,797
Y 1
2 4
1 299,078
N 3

CREATE INDEX cmwtest2 ON CSTSPNP (SPLVL)

select * from cstspnp
where splvl not in ('1',' ')

Access path of file CMWTEST2 was used by query.
for reason code 1.
1 - Record selection.


I think the issue is that usually a NOT IN selects enough records that a table scan is the best
choice.

HTH,

Charles Wilt
--
Software Engineer
CINTAS Corporation - IT 92B
513.701.1307
wiltc@xxxxxxxxxx

-----Original Message-----
From: rpg400-l-bounces@xxxxxxxxxxxx [mailto:rpg400-l-bounces@xxxxxxxxxxxx]
On Behalf Of DeLong, Eric
Sent: Wednesday, July 16, 2008 12:36 PM
To: RPG programming on the AS400 / iSeries
Subject: RE: SQL Help finding missing records

I don't think so..... NOT EXISTS will use an index scan *IF* the fields
referenced in the WHERE of the subquery exist within an index. I would
agree with that statement in the case of the IN predicate....

Eric DeLong

-----Original Message-----
From: rpg400-l-bounces@xxxxxxxxxxxx
[mailto:rpg400-l-bounces@xxxxxxxxxxxx]On Behalf Of vhamberg@xxxxxxxxxxx
Sent: Wednesday, July 16, 2008 10:32 AM
To: RPG programming on the AS400 / iSeries
Subject: RE: SQL Help finding missing records


Caveat emptor!!

Remember that NOT EXISTS, as well as NOT IN, forces a table scan - you
can't tell if something is not there unless you look at everything.
Learned this at the last COMMON, I think.

Now probably and EXCEPTION JOIN can do better, IF you have supporting
indexes.

Regards
Vern

This e-mail transmission contains information that is intended to be confidential and privileged. If you receive this e-mail and you are not a named addressee you are hereby notified that you are not authorized to read, print, retain, copy or disseminate this communication without the consent of the sender and that doing so is prohibited and may be unlawful. Please reply to the message immediately by informing the sender that the message was misdirected. After replying, please delete and otherwise erase it and any attachments from your computer system. Your assistance in correcting this error is appreciated.