Re: SQL Question: NOT IN

Bruce McDougald <brucemcdougald@grocerybiz.com> wrote in article <358AD057.EF0720AD@grocerybiz.com>... > Is the operator NOT IN a resource hog? > > EX: > > select abc > from xyz > where q not in (t,u,v)> The example query will reuqire a sequential scan of table "xyz", even if there is an index on column "q". So, if table "xyz" is large, this query could indeed be a resource hog, causing lots of disk reads and quite possibly flushing the buffer cache. (Under the "right" conditions, the scan will be done as a "light" scan, avoiding the buffer cache altogether.) The query can be sped up if table "xyz" is fragmented across multiple dbspaces, you are running on a multiprocessor paltform and have PDQ enabled (must be running Informix 6.x or greater). HTH Irwin Goldstein Objective Software Systems, Inc. http://www.objectsoft.com