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

I am told (haven't been here long) that was the way the original query was written, and it placed horrible locks and performed dismal as well :-(. Thanks for the suggestion, I'll see what the explain plan says.

Jarek Stoklosa wrote:

> 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 - 11:12:18 CST