× The internal search function is temporarily non-functional. The current search engine is no longer viable and we are researching alternatives.
As a stop gap measure, we are using Google's custom search engine service.
If you know of an easy to use, open source, search engine ... please contact support@midrange.com.



Perhaps the distinction lies in the "style" of IN clause defined... IN can parse a list of values -or- single column rows of a subquery. Row selection is clearly the primary use of an access path (meaning that an index could benefit this use), whereas list value selection is a not necessarily related to row selection. FWIW, I have gotten myself out of the habit of using IN when there are other approaches, because of these recommendations. I tend to use join logic to constrain my results....

Then again, its all just a WAG on my part.

Eric

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


Eric

True enough.

The matter of supporting indexes still holds, I believe. The caution against using NOT IN was given within the last year by a knowledgeable IBM person. So it was given fully in the context of running in SQE.

Again, if there is no index, the choices are not good for the optimizer.

Vern

-------------- Original message --------------
From: "DeLong, Eric" <EDeLong@xxxxxxxxxxxxxxx>

Vern,

I suspect that this is yet another "legacy" issue, that has been corrected on
newer releases. I'm certain that CQE had serious issues with NOT IN, but I'm
not so sure about SQE... Perhaps some of these performance related statements
need to be re-reviewed so that we can un-learn these notions that no longer
apply....

Eric DeLong

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


Charles

I was probably mistaken on the NOT EXISTS, which would take advantage of an
index, if it exists.

And it's good that NOT IN can use and index. I will still stand by what was said
by the IBMer, however, that NOT IN can be a performance drag; better, if
possible, to have a predicate without the NOT. Without an index to use, the
optimizer would have either to create one (table scan PLUS writing the index
out) or to do the table scan.

Lots of ad hoc queries do not have indexes to support them. Of course, that's
another issue.

Vern

-------------- Original message --------------
From: "Wilt, Charles"

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.
--
This is the RPG programming on the AS400 / iSeries (RPG400-L) mailing list
To post a message email: RPG400-L@xxxxxxxxxxxx
To subscribe, unsubscribe, or change list options,
visit: http://lists.midrange.com/mailman/listinfo/rpg400-l
or email: RPG400-L-request@xxxxxxxxxxxx
Before posting, please take a moment to review the archives
at http://archive.midrange.com/rpg400-l.

--
This is the RPG programming on the AS400 / iSeries (RPG400-L) mailing list
To post a message email: RPG400-L@xxxxxxxxxxxx
To subscribe, unsubscribe, or change list options,
visit: http://lists.midrange.com/mailman/listinfo/rpg400-l
or email: RPG400-L-request@xxxxxxxxxxxx
Before posting, please take a moment to review the archives
at http://archive.midrange.com/rpg400-l.



--
This is the RPG programming on the AS400 / iSeries (RPG400-L) mailing list
To post a message email: RPG400-L@xxxxxxxxxxxx
To subscribe, unsubscribe, or change list options,
visit: http://lists.midrange.com/mailman/listinfo/rpg400-l
or email: RPG400-L-request@xxxxxxxxxxxx
Before posting, please take a moment to review the archives
at http://archive.midrange.com/rpg400-l.


As an Amazon Associate we earn from qualifying purchases.

This thread ...

Replies:

Follow On AppleNews
Return to Archive home page | Return to MIDRANGE.COM home page

This mailing list archive is Copyright 1997-2024 by midrange.com and David Gibbs as a compilation work. Use of the archive is restricted to research of a business or technical nature. Any other uses are prohibited. Full details are available on our policy page. If you have questions about this, please contact [javascript protected email address].

Operating expenses for this site are earned using the Amazon Associate program and Google Adsense.