| Cleanup Performance [message #59651] |
Thu, 11 December 2003 08:29  |
Vinny75
Messages: 44
Registered: October 2003
|
Member |
|
|
Hi
We have a ap design issue that resulted in insertion of several hundred thousand rows of records in a table that are not referenced anywhere. They are orphans records that have accumulated over the years. AS such the table holds 3 million rows and we would like to get rid of this 800K records that are taking lot of unwanted space.
I wrote a mock delete procedure which basically retrieves the row_id of these 800K rows and loads it into a pl-sql table. Later loop through that pl-sql table and delete in batch of 1000. My stats indicate, it takes 1-10sec/1000 records to load into pl-sql table and 2 mins to delete every 1000 later.
Can someone suggest me a better alternative..2min/1000 to delete is unacceptable for me. Any help will be appreciated.
thanks
v
|
|
|
|
| Re: Cleanup Performance [message #59652 is a reply to message #59651] |
Thu, 11 December 2003 09:33  |
Thiru
Messages: 1089
Registered: May 2002
|
Senior Member |
|
|
Create & assign a big rollback segment to the transaction.
Do the delete in a ONE SINGLE SQL statement.
COMMIT;
OFFLINE the big rollback segment.
Unnecessary Commits KILL DML performance.
-Thiru
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
