-
We are in the process of testing scripts that would delete approximately 30,000 rows from our database. We are running Oracle8i on an NT server.
We are finding that it is taking about 5 minutes to delete 100 rows. The processor is running at 100% capacity. Does anyone have any ideas about how we could speed this up?
If there is a high level of fragmentation in the indexes could this be causing a slowdown since it would take longer to locate each row to be deleted?
Thanks for any ideas you may have!!
-
Hard to say without seeing the Query Plan. You may not even be using indexes. What process is consuming 100% of the CPU, dbwr, lgwr, user session, etc.? What does each of the disks utilization look like?
-
30000 rows deletion is a small job for Oracle. I think some user process might use the rows when you are trying to delete them. Otherwise it won't take long time.
Also assign exclusive rollback segment for this transaction and try again.
If you are sure that you will not issue rollback, then alter the table in NOLOGGING mode, so that redo will not be generated.
-
Check for cascading constraints as well. if you have them, you may find that deleting from the children up the tree may be less expensive. i doubt that is the root problem, however, unless it's a really extreme constraint cascade.
d.
-
Thanks for all your suggestions. We'll give them a try.
One other question, though. I didn't think the NOLOGGING option could be used when deleting rows. Are you referring possibly to altering the table in some way before performing the deletes?
Thanks for all the input!
-
Nologging, I believe is only with regards to inserts.
