Re: Performance on SELECT..

Thomas Kyte schrieb:
>
> A copy of this was sent to lrako_at_hotmail.com
> (if that email address didn't require changing)
> On Tue, 01 Sep 1998 12:29:15 GMT, you 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

Hi Lalaina,

is it possible to to change the clause into:

        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
> >
> >
> >
>
> This is a hard one. All of the constraints are 'negatives' ( NOT EQUAL to ZERO,
>
> NOT IN this set). Indexes won't help except for the JOIN from A to B.
>
> Suggestion -- use explain plan to see what the query is doing.
>
> Suggestion -- remove the NOT IN and replace it with NOT EXISTS. NOT IN tends to
> get run once per row (full scan of parts_lists2 for each row, not good).
>
> So, the query could be:
> (assuming an index on articles(codenr) and parts_list2(codenr))
>
> SQL> select distinct a.codenr, b.description
> 2 from parts_lists a, articles b
> 3 where a.codenr = b.codenr
> 4 and b.usage <> 0
> 5 and not exists ( select codenr
> 6 from parts_lists2 c
> 7 where B.codenr = c.codenr )
> 8 /
>
> Execution Plan
> ----------------------------------------------------------
> 0 SELECT STATEMENT Optimizer=CHOOSE
> 1 0 SORT (UNIQUE)
> 2 1 FILTER
> 3 2 NESTED LOOPS
> 4 3 TABLE ACCESS (FULL) OF 'ARTICLES'
> 5 3 INDEX (RANGE SCAN) OF 'PARTS_IDX' (NON-UNIQUE)
> 6 2 INDEX (RANGE SCAN) OF 'PARTS_LISTS2_IDX' (NON-UNIQUE)
>
> This will full scan ARTICLES, join it (after appling the where clause usage<>0)
> PARTS_LISTS and then probe the parts_lists2 index to see if we want to keep it..
>
> now, since you have a DISTINCT on the outer query -- you will not get to see the
> first row of the query until you we've computed the last row of the result set
> (the SORT (UNIQUE) step in the execution plan)... So, it'll take a while to get
> to see the answer.. If you can remove the DISTINCT on the outer query (is it
> needed? just asking) you'll get the first rows back from this query very fast.
> >
> >
> >
> >-----== Posted via Deja News, The Leader in Internet Discussion ==-----
> >http://www.dejanews.com/rg_mkgrp.xp Create Your Own Free Member Forum
>
>
> Thomas Kyte
> tkyte_at_us.oracle.com
> Oracle Government
> Herndon VA
>
> --
> http://govt.us.oracle.com/ -- downloadable utilities
>
> ----------------------------------------------------------------------------
> Opinions are mine and do not necessarily reflect those of Oracle Corporation
>
> Anti-Anti Spam Msg: if you want an answer emailed to you,
> you have to make it easy to get email to you. Any bounced
> email will be treated the same way i treat SPAM-- I delete it.

--

Regards

Matthias Gresz :-)

--
Matthias.Gresz_at_Privat.Post.DE

          /|
         / |        
        /| |\
       /||  |\
      / O    |\         
     |        |\ Galopping Horse beats Running Man.
    /          |\
   /      /     |\
  /    __/|      |\
  \°   /  |       |\
   \/_/   |        |\