| slow delete [message #335422] |
Tue, 22 July 2008 02:45  |
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 #335479 is a reply to message #335422] |
Tue, 22 July 2008 05:08  |
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 |
Bill B
Messages: 1971
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 |
 |
BlackSwan
Messages: 26766
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.
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
