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?
Printable View
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?
Faster but unreliable.Quote:
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
Slow but reliable.Quote:
2. run a procedure and delete 10000 rows at a time
HTH
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....Quote:
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.
best is 2nd option wat u thought of - DBA_ADMIN..
Abhay
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
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.
It shud have been Drop old table rather than Truncate...Quote:
Originally posted by hrishy
Hi Abhay
truncate old table
rename new_table to old table
regards
Hrishy
Other wise rename wud error.
Abhay.
Hi Abhay
tahst very thoughtful ..of you..one practical insight into datawarehousing..:-)
regards
Hrishy
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.
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.
Hi,
when number rows is very high then you need an algorythmus which deletes endless number of rows without waisting to much RBS.
Pseudo-code:
LOOP
delete from table where rowcount <= 1000;
commit;
WHEN sql%rowcount < 1000 then exit;
END LOOP;
This is fast too
Orca
:cool:
After this rebuild indexes
I guess this issue has been discussed recently.
http://www.dbasupport.com/forums/sho...ghlight=delete
I have done something similar recently..
Deleting 17GB worth of rows on a 35GB table.. Deleting takes forever.
In my case, I had data by periods to delete.. So, exported required data, created a new table, dumped into it partitions, enabled constraints etc., and once verified, renamed the old table (safety) and then renamed the new table as well.. then created indexes (very crucial part for the database as this was another 20GB).
But for the time (weekend) the table was not accessed at all. Took about 16 hrs in total..
You need a strategy for sure as something this huge can be dependant on space, availability, users, access, time etc., and application which no one can know perfectly but you..
Thanks, ST2000
The FK constraints in a DW are probably not enabled or valiated though - usually set to RELY only, in which case there is really no overhead associated with them.Quote:
... 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...
Number 2 option will fall over... time after time, as you are performing a commit in a cursor. Oracle RECOMMEND you DON'T do this. Parition... Open Cursor, fetch, COMMIT, close cursor, repeat. Only commiting every million records or so. But deletting is SLOW, SLOW SLOW.... get into Partitioning and Truncate.Quote:
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
or
2. run a procedure and delete 10000 rows at a time
Which is faster?