Performance of delete operation

[Vimala]

Hello,

My database is running in archive mode. I have a table with large number of rows in it. Every day I need to delete the previous day's rows, which are around 400,000. When I do the delete operation, database is generating so many archive log files and running out of disk, also the operation is taking long time. I don't want to archive log this operation. Is there any option which I can escape logging the delete operation? Or is there any other way that I can achieve the performance. My table has no LOB data types and it has just character, number and date fields.

I will appreciate your help.

Thanks,
Vimala

[gpsingh]

1) Deleting 400,000 rows generating a lot of archived file ..????? check the size of ur redo log file.

2) Why not partiton the table

[Vimala]

Yes. My log file size is only 5MB and database is generating lots of archive log files when I delete 400 thousand records at a time.

What is the need in partitioning table? I don't need the data in it for more than one day.

[marist89]

Is TRUNCATE an option?

[shaft]

Truncating the table will bypass writing to the Archivelogs and take a fraction of the time. Truncating does not allow for any sort of logic, it will simply empty the table. I truncated a table with 1.6 million rows in under 10s. Your performance will vary but it will be appreciably faster.

[Vimala]

No, I don't want to delte all the records in that table.

I want to delete only yesterday's data and I want to keep today's data which could be around 100 thousand records at the time of deletion. The table should have only today's data.

[marist89]

Oh, in that case, partitioning would be perfect for you.

You would store a days worth of data in each partition. You can then truncate one partition worth of data without affecting the rest of the data in another partition.

PS. make sure you use local indexes, it will make you life much easier...

[ybadejo]

You might want to try this steps.

1 Check to see if users are logged on
if not
2. shutdown immediate
3. comment outout archiving in Init.ora file
4. startup restrict mount
5. alter database noarchivelog
6. alter database open
7. do your delete command
once done
8. shutdown immediate
9. uncomment out archiving in Init.ora file
10. startup mount
11. alter database archivelog
12. alter database open
13. alter system disable restricted session

Hopefully this works for you.

[marist89]

Originally posted by ybadejo
You might want to try this steps.

1 Check to see if users are logged on
if not
2. shutdown immediate
3. comment outout archiving in Init.ora file
4. startup restrict mount
5. alter database noarchivelog
6. alter database open
7. do your delete command
once done
8. shutdown immediate
9. uncomment out archiving in Init.ora file
10. startup mount
11. alter database archivelog
12. alter database open
13. alter system disable restricted session

Hopefully this works for you.

I would discourage using this method. First, you have to bounce your database. That by itself is bad enough. However, by switching out of archivelog mode, you have now ruined your chances for any kind of recovery using archived redo logs.

[ybadejo]

well, I will assume you have a good backup before doing.