Re: Performance on SELECT..

From: Thomas Kyte <tkyte_at_us.oracle.com>
Date: Wed, 02 Sep 1998 15:05:29 GMT
Message-ID: <35f3594f.6593270@192.86.155.100>

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
> 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. Received on Wed Sep 02 1998 - 10:05:29 CDT