Re: Why does a simple delete takes 12 hours and longer ?

Kathryn,

  The sql you have suggested has some deficiencies.

1. The sub-query returns more than one column which is compared against a single column. This will result in "too many values" error.
2. instead of the stmt "b.docnum is null" it can be re-written using not exists clause, if that is what is the requirement.

Regards
Rajagopal Venkataramany   

On Fri, 04 Aug 2000 06:04:20 -0800, ORACLE-L_at_fatcity.com wrote:

> Andreas, you could probably get your response down to seconds,
> rather than minutes, if you do an outer join that uses an index
> on the main table. I would do a cursor loop only if I had other
> processing to do on those rows.
>
> 250K and 300K just aren't that many records and it shouldn't take
> 6-7 minutes, let alone 12 hours, if the tables are indexed
> correctly, and you write a good delete statement.
>
> delete tablea where docnum in
> (select a.docnum, b.docnum
> from tablea a, tableb b
> where a.docnum = b.docnum (+)
> and b.docnum is null)
> /
>
> (do I have that plus in the right place? I sometimes have
> trouble with L and R, too. <g>)
>
> Your indexes must begin with docnum. Anyone, will the b. table
> use an index even with that "and b.docnum is null"?
>
> (Harrrrrumph. In *MY* day, we had to have 6-7 *second*
> turnaround on queries or the system wasn't accepted. Uphill!
> Both ways! danged whippersnappers.)
>
> >
> > I am sorry to say that there are no such things as stupid databases
> >
>
> Now, now, Phil. <g>
>
> BTW, thanks a bunch, people, now I've got that idiotic theme song
> going through my head!
>
> KLZ
> Certified ORACLE Geezer
> (...for a threeeee hour tour...)(to continue the analogy, DB
> Manager=Duncan MacLeod)(...the Minnow would be lost...)
> --
> Author: Kathryn L. Zimmerman
> INET: kzimmer1_at_csc.com
>
> Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051
> San Diego, California -- Public Internet access / Mailing Lists
> --------------------------------------------------------------------
> To REMOVE yourself from this mailing list, send an E-Mail message
> to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
> the message BODY, include a line containing: UNSUB ORACLE-L
> (or the name of mailing list you want to be removed from). You may
> also send the HELP command for other information (like subscribing).

Regards
Rajagopal Venkataramany