Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.server -> Help trying to eliminate a full table scan and optimize query

Help trying to eliminate a full table scan and optimize query

From: hellothereyou <hellothereyou_at_yahoo.com>
Date: Fri, 24 Nov 2000 21:22:21 -0800
Message-ID: <3A1F4C8D.E4835BA@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 Fri Nov 24 2000 - 23:22:21 CST

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US