Home » SQL & PL/SQL » SQL & PL/SQL » slow delete (Oracle XE, Win2003 Enterprise)
slow delete [message #335422] Tue, 22 July 2008 02:45 Go to next message
majstoru
Messages: 68
Registered: October 2006
Location: Serbia
Member
Hi,

I have small problem with OracleXE when I want to delete 10000 documents from table (master table, connected via foreign key to detail table and each master record have one detail record).

When I call DELETE FROM MasterTbl WHERE Type = 'XYZ' oracle is erasing about 30 min and after that time nothing is hapend and I kill proces and few times like there but job is not executed.

I have index on filed Type on MasterTbl, where is my error?

Thanks in advance...
Re: slow delete [message #335447 is a reply to message #335422] Tue, 22 July 2008 03:58 Go to previous messageGo to next message
S.Rajaram
Messages: 1027
Registered: October 2006
Location: United Kingdom
Senior Member
Check this link.

http://asktom.oracle.com/tkyte/unindex/index.html

Hope this helps.

Regards

Raj
Re: slow delete [message #335461 is a reply to message #335422] Tue, 22 July 2008 04:26 Go to previous messageGo to next message
dr.s.raghunathan
Messages: 540
Registered: February 2008
Senior Member
try with disabling the contraint you have created like foreign key
Re: slow delete [message #335479 is a reply to message #335422] Tue, 22 July 2008 05:08 Go to previous messageGo to next message
majstoru
Messages: 68
Registered: October 2006
Location: Serbia
Member
Hi,

Here is solution, I was created index on a foreign key in my DetailTbl and my statement is execude for a few secounds!

Thanks for help...
Re: slow delete [message #335789 is a reply to message #335479] Wed, 23 July 2008 15:00 Go to previous messageGo to next message
Bill B
Messages: 1482
Registered: December 2004
Senior Member
majstoru wrote on Tue, 22 July 2008 05:08
Hi,

Here is solution, I was created index on a foreign key in my DetailTbl and my statement is execude for a few secounds!

Thanks for help...


As a very good rule, is a child column has a foreign key to a parent, the child column should always be indexed. otherwise a delete must always do a full table scan for every row deleted.
Re: slow delete [message #335790 is a reply to message #335422] Wed, 23 July 2008 15:06 Go to previous message
BlackSwan
Messages: 25036
Registered: January 2009
Location: SoCal
Senior Member
> otherwise a delete must always do a full table scan for every row deleted.
Most definitely true & in the process it will completely LOCK the child table.
Previous Topic: difference between these queries
Next Topic: buffer overflow, limit of 1000000 bytes
Goto Forum:
  


Current Time: Sun Dec 04 06:50:15 CST 2016

Total time taken to generate the page: 0.12907 seconds