Message-Id: <10579.113846@fatcity.com> From: "Kathryn L. Zimmerman" Date: Thu, 03 Aug 2000 15:34:50 -0400 Subject: 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. ) 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. 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...)