Reply to Performance on SELECT..(ANALYZE)

Hi everybody !

First I would like to thank anyone who helped me .

A few remarks:

following your recommendations , the cost of the query dropped from 680000 to 1648 !!

I will avoid using NOT IN in my queries !

the DISTINCT was meaningless !

Even creating an index on articles, using EXPLAIN PLAN showed me that it is not used.

I willl still need to check if I can change into b.usage > 0 !!

Many thanks !!!

If I do ANALYZE table, how can I exploit the results ?

Lalaina

> >
> > 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
>

-----== Posted via Deja News, The Leader in Internet Discussion ==----- http://www.dejanews.com/rg_mkgrp.xp Create Your Own Free Member Forum Received on Thu Sep 03 1998 - 08:43:30 CDT