• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 463
  • Last Modified:

Optimize delete statement on 37 millions rows

Hello,

I have to delete 34 millions rows on a table SNMP_LOG on the column first_date < 28/02/2011.
How can I do it? I haven't enough space on the volume disk and This statement can generate a lt of archivelogs.
Do you have an example, please?

Thanks

Regards

bibi
0
bibi92
Asked:
bibi92
  • 3
  • 2
  • 2
1 Solution
 
slightwv (䄆 Netminder) Commented:
How many rows are left after the delete?

If you don't have the disk for that amount of redo, your options are pretty limited.

You can limit UNDO with a loop ant commiting often but not redo.

You can turn off logging on the table but the is dangerous and not advised on production systems.
0
 
sventhanCommented:
How many rows you have on this table? Is the table partitioned?

1) you can create a new table with the wanted rows using CTAS
2) Delete the rows in a small batch, 100K at a time.
0
 
slightwv (䄆 Netminder) Commented:
1- why I asked how many rows are left.
2- won't reduce the amount of redo generated if archiving.
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

 
sventhanCommented:
couple more

3) How many columns do you have in this tabble?
4) No of indexes/triggers etc?
5) Size of the table?

0
 
bibi92Author Commented:
1)3millions
3) 8 columns
4) No triggers, 1 index
5) 15go

Thanks

bibi
0
 
sventhanCommented:
< 1)3millions



The best option would be create a new table.

1) Create table SNMP_LOG_temp  as
select all_8_columns from  SNMP_LOG
where first_date >= 28/02/2011 --- whatever your filter criteria to select the 3 million rows

2) rename snmp_log to snmp_log_old and drop the index on snmp_log

3) rename snmp_log_temp to snmp_log and create the index

4) run  statistics


0
 
bibi92Author Commented:
Thanks bibi
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

  • 3
  • 2
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now