Re: Performance on SELECT..

From: <prochak_at_my-dejanews.com>
Date: Wed, 02 Sep 1998 13:53:09 GMT
Message-ID: <6sjik5$hp8$1@nnrp1.dejanews.com>

In article <6sgpar$9hs$1_at_nnrp1.dejanews.com>,   lrako_at_hotmail.com wrote:
> 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
>

Are there indices for the tables being used?

Have the tables been ANALYZEd recently, including the index?

Can you rearrange the query to avoid bad constructs?   the b.usage <> 0 prevents the optimizer from using   any index that might exist on the USAGE column. Another   bad one is the NOT IN construct.

The NOT IN clause is likely you performance killer. The
DISTINCT was a good try but it doesn't really help. Oracle
will still do a full scan of the parts_list2 table. (you can

verify this using AUTOTRACE or doing an EXPLAIN PLAN.)

See if you can rewrite that using a NOT EXISTS clause.

it might look something like this:

    AND NOT EXISTS

       (SELECT 'anything'
         FROM parts_lists2
         WHERE a.codenr = parts_lists2.codenr) ;

NOT IN is a construct that is best to avoid.

--
Ed Prochak
Magic Interface, Ltd.
440-498-3702

-----== 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 - 08:53:09 CDT