Home » RDBMS Server » Server Administration » Deletion of records from big table
Deletion of records from big table [message #230260] Wed, 11 April 2007 06:02 Go to next message
jaydba
Messages: 86
Registered: September 2006
Location: UK
Member

I have archived some tables in archive area in another schema. Now I would like to remove millions of records (old or historical) from A schema of the concern table. These records are preserved in an archive area.

I tried the follwing statement for big table but it takes so much time to process this data and it does not respond anything.

DELETE FROM J55 WHERE D1 IN (SELECT D1 FROM B.J55)

What is the best way to remove records faster.

1) DO I need to set large rollback segments

2) DO I need to set a parallel DML session

3) Do I need to set a large retention period

4) Do I need to use script for removing records by setting interval for commit

I am confused here to remove millions of records from the big table. Please suggest me the best approch to fulfill my requirement.

Your help would be highly appreciated.

Re: Deletion of records from big table [message #230264 is a reply to message #230260] Wed, 11 April 2007 06:13 Go to previous messageGo to next message
tahpush
Messages: 961
Registered: August 2006
Location: Stockholm/Sweden
Senior Member

A very informative discussion on asktom
Re: Deletion of records from big table [message #230285 is a reply to message #230264] Wed, 11 April 2007 07:41 Go to previous messageGo to next message
jaydba
Messages: 86
Registered: September 2006
Location: UK
Member

I have gone through this thread and wanted to verify about it before implemeting.

I have one of the table with size of 18GB and needs to remove records around 10GB.

How big size of rollback segment is required to remove this records. How to size and estimate the size of RBS?

I would prefer to use the approch no. 1 mentioned by Tom in that thread.

SET TRANSACTION USE ROLLBACK SEGMENT Rbig;
DELETE FROM j55 WHERE D1 in (select d1 from B.j55);
COMMIT;

Your help would be highly appreciated.
Re: Deletion of records from big table [message #230307 is a reply to message #230285] Wed, 11 April 2007 08:52 Go to previous messageGo to next message
tahpush
Messages: 961
Registered: August 2006
Location: Stockholm/Sweden
Senior Member

So what about
Quote:
create table new_table unrecoverable as select * from old_table where ....;
drop table old_table;
rename new_table to old_table;
create index old_table_idx1 on old_table(c1,c2) unrecoverable parallel 5;
.....

NO log on that, just move the data to a new table, drop/rename old/new, create indexes as
fast as possible without log.


Re: Deletion of records from big table [message #230440 is a reply to message #230260] Thu, 12 April 2007 00:45 Go to previous message
michael_bialik
Messages: 611
Registered: July 2006
Senior Member
Just use NOLOGGING instead of unrecoverable.
Previous Topic: ORACLE SERVICES
Next Topic: oracle 10gr2 companion
Goto Forum:
  


Current Time: Sat Dec 10 12:33:51 CST 2016

Total time taken to generate the page: 0.09929 seconds