-
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
After this rebuild indexes
-
Last edited by sreddy; 03-14-2003 at 11:57 AM.
Reddy,Sam
-
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
-
... 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...
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.
-
Re: delete 40million rows
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?
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.
OCP 8i, 9i DBA
Brisbane Australia
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|