Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.server -> Re: Possible Performance Improvement to Select Statement

Re: Possible Performance Improvement to Select Statement

From: D Rolfe <dwrolfeFRUITBAT_at_orindasoft.com>
Date: Thu, 03 Feb 2005 10:39:39 +0000
Message-ID: <4201FF6B.2090400@orindasoft.com>


Paul,

> The following select statement works for me but it takes a lot of
> process time and is rather slow. I'd like to know if there's another
> way of doing the same query only faster and less process time
> intensive.
>
> The following script contains 2 queries that use 2 tables. One query
> is used only to exclude a certain range of data.
>
> select distinct pkvk.CUST_JOB_NR
> from pkvk,pkvp
> where pkvk.JOB_STATUS != 9
> and pkvp.CUST_JOB_NR = pkvk.CUST_JOB_NR
> and pkvp.CUST_JOB_TYPE = pkvk.CUST_JOB_TYPE
> and pkvk.CUST_JOB_NR
>
> not in (select distinct pkvk.CUST_JOB_NR
> from pkvp,pkvk
> where pkvk.JOB_STATUS != 9
> and pkvp.CUST_JOB_NR = pkvk.CUST_JOB_NR
> and pkvp.CUST_JOB_TYPE = pkvk.CUST_JOB_TYPE
> and pkvp.POSITIONS_STATUS != 9)
>

Use 'NOT EXISTS' instead of 'NOT IN'.

Not exists stops searching when it finds a matching record (in theory :) ).

David Rolfe
Orinda Software
Dublin, Ireland Received on Thu Feb 03 2005 - 04:39:39 CST

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US