Performance of delete operation

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

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

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.

Is TRUNCATE an option?

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.

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.

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...

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.

Quote:

---

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.

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

Quote:

---

Originally posted by ybadejo
well, I will assume you have a good backup before doing.

---

Regardless of whether you have a good backup or not, is irrelevent. Once you take the database OUT of archivelog mode, you are only able to recover UP TO the point you turned archiving off. If your database dies AFTER you switch archiving back on, you are SOL for recovery! Understand? http://www.dbasupport.com/forums/ima.../2001/11/9.gif

Hey
I thought we have it in restricted mode and moreso nousers is on it. so if anyrecovery needs to be made once their is a good backup. wont you be fine???.

hello,
try this:
alter table . nologging;

all the activity on this table will not generate arc log until you again do
alter table . logging;

Is 'alter table xxx NOLOGGING' an option?

NOLOGGING is one of the most mis-understood operations in the Oracle Software. See http://technet.oracle.com/docs/produ...dlins.htm#4418 for operations that respect nologging and operations that don't.

Quote:

---

Originally posted by ybadejo
Hey
I thought we have it in restricted mode and moreso nousers is on it. so if anyrecovery needs to be made once their is a good backup. wont you be fine???.

---

No. Once you switch into noarchivelog mode, you can not recover past that point.

Quote:

---

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.

---

that would be one of worst strategy

Here we have Oracle 7.3. Do we have partitioning option in 7.3?

If we have.... after truncating yesterday's partition, I would not need that partition, so, do you mean I should delete that parition? instead of that, I better play with two tables one for today and another for yesterday and delete yesterday's table right away?

I can not shutdown database and switch out of archive log mode.

Mr. hbk_emp,

Alter table . nologging, will not work for normal delete operation. It works only if we have to DELETE on LOBs in NOCACHE NOLOGGING mode stored out of line (not in the table). Am I correct?

Quote:

---

Originally posted by Vimala
Here we have Oracle 7.3

---

This is a perfect example of why you should specify your OS, OS Version, DB version in any post.

Partitioning (the way you want to use it) was not a feature until version 8.0.x.

Do you have an on delete trigger? If so look at what it is doing. If you are doing alot of DML in an on delete trigger then it would generate alot of redo. I would minimize the DML in any on delete triggers that you have and take a serious look at partitioning. I don't believe that 7.3.4 has partitioning, I think that it was added with Oracle 8. So you may need to upgrade, since Oracle 7.3.4 is being desupported anyway, you should be on at least 8i.

Heck, if you're upgrading, upgrade to 9.0.1.2.

Operating System is HP UNIX. We can not upgrade oracle to latest version because our application supports only 7.3. We might upgrade the application in the near future.

But my question is why not I use another table instead of using paritions. How do I benefit by paritioning in this case?

And "Alter table . nologging" is not going to help me anyway since I don't have LOB.

I feel upgrading Oracle to latest version may not solve my problem.

Since partitions can be thought of as mini-tables, you could do it with many little tables. I think v7 has the concept of partition-views which allows you to create a view that is a union of all the partitions.

IMHO, partitioning will solve your problem if you upgrade, but what do I know... http://www.dbasupport.com/forums/ima...2001/11/10.gif

[Edited by marist89 on 11-27-2001 at 03:47 PM]

say your table name as emp
Rename your original table to emp_prod
create table emp as select * from ....(give the condition )
truncate the emp_prod ;
if you have any triggers/procedure etc have them and recreate them ,,,

I don't have any triggers on this table.

Create table....as select * from.... may be this is good idea if it won't generate more log files. I should try this. Thank you very much for all your help.

Looking forward for more suggestions.

Thanks again,
Vimala

Vimala,
How does the data get generated in this table ?
I have been able to do something similar to this in the past :

Lets say your table is DAILY_RECORDS.
Create a "CREATE TABLE..." script. This script should be able to accept a parameter for the table name.
Every midnight, execute this script to create a new table with some logic (say the date) built in the tale name. eg. DAILY_RECORDS_11272001. This parameter should automatically change everyday.
After this table (say DAILY_RECORDS_11272001) gets created, create a synonym DAILY_RECORDS that will point to DAILY_RECORDS_11272001.
Truncate the previous day's table. You may even decide to drop the previous day's table.
Because there exists a synonym same as your table name, everything else can remain unchanged.

I hope you have understood the gist of this method. It might not be possible for you to implement it if your application restricts doing it this way.

[Edited by svk on 11-27-2001 at 07:50 PM]

Wonderful idea. I appreciate your help. I should follow your idea. Thank you very much for all your help.