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

The reason that the FULL TABLE SCAN occurs on the RAW_EXCEPTIONS table is that the EXISTS clause must be evaluated for every row in RAW_EXCEPTIONS. The EXISTS clause is looking for a true/false result. Based on the query, you want to eliminate all rows from RAW_EXCEPTIONS that are found in FIXED_EXCEPTIONS.

The IN clause would be a good possibility, but I would not use it if you are expecting a large result set from the sub-query. You could try the use of a PL/SQL process, but based on the query, I am not sure you could do this. A question that I have is what is the DELETE process trying to accomplish? I have a guess that it is removing records from RAW_EXCEPTIONS that have found their way into FIXED_EXCEPTIONS. If this is so, how does the data get moved? Is there a timestamp for the data in FIXED_EXCEPTIONS? If so, this might help out this process tremendously.

Hope this helps.

Dave Grzebien
Expert Technical Consultants, Inc
dave_at_etci.net

hellothereyou wrote:

> 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 Sun Nov 26 2000 - 20:41:53 CST