OraFAQ Forum: Server Administration » Deletion of records from big table

Home » RDBMS Server » Server Administration » Deletion of records from big table

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

Deletion of records from big table [message #230260]

Wed, 11 April 2007 06:02

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.

Report message to a moderator

Re: Deletion of records from big table [message #230264 is a reply to message #230260]

Wed, 11 April 2007 06:13

tahpush
Messages: 961
Registered: August 2006
Location: Stockholm/Sweden

Senior Member

A very informative discussion on asktom

Report message to a moderator

Re: Deletion of records from big table [message #230285 is a reply to message #230264]

Wed, 11 April 2007 07:41

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.

Report message to a moderator

Re: Deletion of records from big table [message #230307 is a reply to message #230285]

Wed, 11 April 2007 08:52

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.

Report message to a moderator

Re: Deletion of records from big table [message #230440 is a reply to message #230260]

Thu, 12 April 2007 00:45

michael_bialik
Messages: 621
Registered: July 2006

Senior Member

Just use NOLOGGING instead of unrecoverable.

Report message to a moderator

Previous Topic:	ORACLE SERVICES
Next Topic:	oracle 10gr2 companion

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Fri Sep 20 03:59:39 CDT 2024