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

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...) Received on Thu Aug 03 2000 - 14:34:50 CDT