Re: Possible Performance Improvement to Select Statement

Paul Izzo wrote:
> 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)

Isn't this equivalent to

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 pkvp.POSITIONS_STATUS = 9;

i.e only include records that match to one with a positions_status of 9 rather than exclude ones that don't?

Even if it isn't you don't need the distinct in the not in query (so you can possibly avoid a sort) and as others have said not exists might well be faster.

Niall Litchfield
Oracle DBA
http://www.niall.litchfield.dial.pipex.com Received on Thu Feb 03 2005 - 08:59:08 CST