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

From: Jarek Stoklosa <stoklosa_at_sigma.wsb-nlu.edu.pl>
Date: Sat, 25 Nov 2000 13:34:49 +0100
Message-ID: <8vobjg$ahc$1@news.tpi.pl>

Do you try this?:
DELETE FROM raw_exceptions
> WHERE raw_exceptions.seqno in (SELECT seqno FROM fixed_exceptions);

Uzytkownik "hellothereyou" <hellothereyou_at_yahoo.com> napisal w wiadomosci news: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 Sat Nov 25 2000 - 06:34:49 CST