Re: Help trying to eliminate a full table scan and optimize query

From: Jonathan Lewis <jonathan_at_jlcomp.demon.co.uk>
Date: Mon, 27 Nov 2000 20:59:02 -0000
Message-ID: <975355632.8388.1.nnrp-07.9e984b29@news.demon.co.uk>

It is common 'knowledge', but unfortunately not always true that an EXISTS clause is faster than an IN clause.

Given suitable statistics, the following is likely to be quicker:

    DELETE FROM raw_exceptions re
    WHERE re.seqno in (

        select fe.seqno FROM fixed_exceptions fe     )

Given suitable indexes (which means a unique one on fixed_exceptions, and a non-unique on raw_exceptions) which looks a little unlikely given you explain plan, the following could be even faster:

delete from (

    select re.seqno from

        fixed_exceptions fe,
        raw_exceptions re

    where fe.seqno = re.seqno
);

In either case your primary target is to get Oracle to choose to scan the fe table and delete from the re table for each fe.seqno, rather than scanning the re table and checking to see if an fe.seqno exists.

--
Jonathan Lewis
Yet another Oracle-related web site:  http://www.jlcomp.demon.co.uk

Practical Oracle 8i:  Building Efficient Databases

Publishers:  Addison-Wesley
Book bound date: 8th Dec 2000
See a first review at:
http://www.ixora.com.au/resources/index.htm#practical_8i
More reviews at: http://www.jlcomp.demon.co.uk/book_rev.html



hellothereyou wrote in message <3A1F4C8D.E4835BA_at_yahoo.com>...


>We are running Oracle 8.0.5 on Solaris 2.6

>A developer has written the following code and it takes a horrendous

>amount of time!

>Both tables are analyzed, and both are indexed on the columns in the

>where clause.

>

>Below is the query and then the explain plan output.

>Is there any way to get this query to work better?

>

>THANKS!

>

>DELETE FROM raw_exceptions

> WHERE exists (SELECT 1 seqno FROM fixed_exceptions where

>fixed_exceptions.seqno = raw_exceptions.seqno);

>

>DELETE STATEMENT Optimizer=CHOOSE (Cost=13487 Card=351344

>Bytes=18972576)

>DELETE OF RAW_EXCEPTIONS

>FILTER

>TABLE ACCESS (FULL) OF RAW_EXCEPTIONS (Cost=13487 Card=351344

>Bytes=18972576)

>INDEX (RANGE SCAN) OF FSJR_IDX (NON-UNIQUE) (Cost=1 Card=1 Bytes=18)

>

>

>

>

Received on Mon Nov 27 2000 - 14:59:02 CST