delete 40million rows

[dba_admin]

I have a table with 80 million rows of data. I need to delete about 40 millions. I can do either

1. set a huge rollback segment and delete 40millions at once

or

2. run a procedure and delete 10000 rows at a time

Which is faster?

[adewri]

Originally posted by dba_admin
I have a table with 80 million rows of data. I need to delete about 40 millions. I can do either

1. set a huge rollback segment and delete 40millions at once

Faster but unreliable.

2. run a procedure and delete 10000 rows at a time

Slow but reliable.

HTH

[culonbu]

how about

1. create new table as select * from exiting table
where records are the one you wnat to keep.
2. drop existing table.
3. rename new table to existing table.

[abhaysk]

Originally posted by culonbu
how about

1. create new table as select * from exiting table
where records are the one you wnat to keep.
2. drop existing table.
3. rename new table to existing table.

Big headache if FK Constraints are there....

best is 2nd option wat u thought of - DBA_ADMIN..

Abhay

[hrishy]

Hi Abhay

How about disbaling the constraints and then doin somethin like this

create a table

insert /*+append*/ into new_table
select * from old table the rows you wanna kep

truncate old table


rename new_table to old table

enable constraints onec again

regards
Hrishy

[abhaysk]

Hrishy:

If it were only 1 FK associated with it then its okie...but in Datawarehousing projects u will have hell lots of tables linked with so many other tables and the table in question in turn referencing many other tables...then it wud not be wise..

rather delete records and put a exception part...and commit records for every 100000 records..

but yes if ther are no constraints, then ur method wud be good...

ahh now there it comes, if the table is Index organised....u then have headache of building indexes again...

Abhay.

[abhaysk]

Originally posted by hrishy
Hi Abhay


truncate old table


rename new_table to old table

regards
Hrishy

It shud have been Drop old table rather than Truncate...
Other wise rename wud error.

Abhay.

[hrishy]

Hi Abhay

tahst very thoughtful ..of you..one practical insight into datawarehousing..:-)

regards
Hrishy

[dba_admin]

I actually did:

1. drop all indexes and constraints.
2. delete 10 millions rows at a time
3. rebuild indexes with parallel/nologging option

It works for me.

[AJW_ID01]

I take it you made sure you didn't drop any constraints that had a CASCADE ON DELETE setting? Otherwise, you could have some orphans on child tables.

- Tony.