Optimize delete statement on 37 millions rows


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?



Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

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.
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.
slightwv (䄆 Netminder) Commented:
1- why I asked how many rows are left.
2- won't reduce the amount of redo generated if archiving.
Big Business Goals? Which KPIs Will Help You

The most successful MSPs rely on metrics – known as key performance indicators (KPIs) – for making informed decisions that help their businesses thrive, rather than just survive. This eBook provides an overview of the most important KPIs used by top MSPs.

couple more

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

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


< 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


Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
bibi92Author Commented:
Thanks bibi
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Oracle Database

From novice to tech pro — start learning today.