Re: Performance on SELECT..

SELECT distinct a.codenr , b.description   FROM parts_lists a , articles b
 WHERE a.codenr = b.codenr
   AND b.usage <> 0
   AND EXISTS

       (SELECT 'Dummy'
	  FROM parts_lists2 c where c.codenr = a.codenr) ;

HTH Philippe

lrako_at_hotmail.com wrote in article <6sgpar$9hs$1_at_nnrp1.dejanews.com>...
> Hi all !
>
> I have the following problem of performance.
>
> I would like to have the code numbers (and description) of parts, whose
usage
> <> 0 and existing in 'parts_lists' and not existing in 'part_lists2'.
Here's
> the SELECT statement I wrote:
>
> SELECT distinct a.codenr , b.description
> FROM parts_lists a , articles b
> WHERE a.codenr = b.codenr
> AND b.usage <> 0
> AND a.codenr NOT IN
> (SELECT DISTINCT codenr
> FROM parts_lists2) ;
>
>
> Parts_lists has 551000 rows
> Articles has 30000 rows
> parts_list2 has 50000 rows
>
> The above query is now running for more than 3 hours.
> Does anyone have an idea to improve performance ??
> (indexes, pl/sql..)
>
> Thanks for hints.
>
> Lalaina
>
>
>
>
>
>
> -----== Posted via Deja News, The Leader in Internet Discussion ==-----
> http://www.dejanews.com/rg_mkgrp.xp Create Your Own Free Member Forum
>
Received on Wed Sep 02 1998 - 00:59:16 CDT