Skip to content

How to perform large deletes in oracle ? knowledge article for the new oracle DBA





You are a new oracle DBA and you had to delete data from some production database table . you have got all the necessary change management approvals. if you dont know what is change management then google for ITIL concepts . so you have your approvals and your boss also agrees and now you found out that this  table is really very very huge with billions of records. what would you do? you have couple of options

option 1 ) go ahead and execute the delete statement and after many many hours of execution you might get the error that undo tablespace is full or space errors etc and the delete statement fails and then takes hours to rollback all the changes done by the delete and you have to reply to your boss about what you did for the last 3 days.

option 2:  before starting the delete statement you can look into the dba_segments table and find out the size of the table. If the table size is big then you can issue a select query to find out how many records your delete statement would delete. Then you can find out the total number of records in the table. If you are going to almost delete all the records from the table then it is better for you to copy the good records into a temporary table and then truncate the first table and then copy the good records from the temporary table into the first table.

option3: write a pl/sql procedure and use the forall bulk delete advanced pl/sql option to bulk delete 1000 or 2000 or 3000 at a stretch. for this you definetly need time .so explain to your manager that the table is very big and that you have to write a pl/sql procedure. test it on your test server and then execute it on production.

Below discussion explains the above points

Hello all,

The problem of deleting large amount of data from database appeared several times in the forum, but none of them reflects my problem.

I have a table which is appended of ca. 1 700 000 records every day. Every day should also be oldest records deleted, ca. 1 700 000 of them. Total number of records in this table is constant, ca. 0,6 billion. There is no dependency between this table and any other Oracle object, and the only operations performed on this table is adding new records, removing oldest and selecting them. One column of this table is indexed, so adding of removing data takes some time to update index. But it is not the issue.

The problem is in deleting oldest records. When I simply issue a DELETE command I get a message that undo tablespace is to small, transaction rollback will not be possible and therefore records cannot be deleted. This is normal and expected effect, because Oracle general philosophy is full data recovery. But in my particular case this recovery policy is not needed. I simply want to irrecoverably delete large amount of data. If something happens during transaction then this is not a problem, because such failed operation can be easily corrected on application (not Oracle) level.

I couldn’t find in the web how to "switch off" rollback, undo, or whatever it is called. Oracle gurus told me that probably it is not possible and the only solution is to increase undo tablespace. But I can’t believe that there is no such mechanism in Oracle that allows ultimate and irrecoverable data deletion. Deleting records in smaller sets, by 100 000 for example, takes too long because indexes must be updated after every commit. In the ideal case I would like oldest records to be deleted, then added new ones, and finally updated index and committed the whole operation.

Do you have any idea how to achieve this? Or should I organize my data in a different way?

Any hints will be greatly appreciated.

Greg

 
Hi,

You need to use partitioned table.
Create a table with partitions by day (or by week, by month – for period which you want delete/drop), and instead of delete rows, you can drop the old partition which you don’t want to keep. You can drop or place his tablespace in offline mode, like this you keep data, wihtout any access.
One more thing, you need to anticipate on the following period with creating the following partition.

Nicolas.

PS1 : you can see some sample here http://download-west.oracle.com/docs/cd/B10501_01/server.920/a96540/statements_73a.htm
PS2 : for partition usage, you need to have Entreprise Edition with an additional paid option.

Message was edited by:
N. Gasparotto

 

 

Romain VILCOQ  

Posts: 190
Registered: 05/24/00

 

  Re: Deleting large number of records
Posted: 13-Apr-2006 08:16   in response to: user451376
 
  Reply

 

Hi, I agree with Nicolas on using partitionning.

But , first simply ask your prefered DBA to size the UNDO tablespace according to your buissiness needs.
You can’t switch off rollback as they are needed to statisfy with the ACID test.

 

 

     

 

Scott Zheng  

Posts: 1,061
Registered: 01/04/06

 

  Re: Deleting large number of records
Posted: 13-Apr-2006 12:25   in response to: Romain VILCOQ
 
  Reply

 

using partitionning is a good way.

if you don’t want to use partitionning, and number of deleting data much large than keeping data, for example, 10,000,000 rows in this table, and 8,000,000 rows needs to be deleted. You can do the steps as following:
1. create new table
create table <newTable> as select * from <oldTable> where …
2. drop old table
3. rename <newTable> to <oldTable>
4. create the index.

This way just uses a little of UNDO_TS.

 

 

gopalora  

Posts: 972
Registered: 11/10/99

 

  Re: Deleting large number of records
Posted: 13-Apr-2006 18:21   in response to: Scott Zheng
 
  Reply

 

There are two ways to solve this problem :-
CASE 1:-
Create new table like user477610 mentioned.

CASE 2:-
DELETE FROM myfata$$ WHERE rownum < 100000; –Start with a small number and gradually increase to see how many rows you can delete in one shot without hitting the UNDO problem.

Most efficient in your case is CASE 1, determine a value to which the table can grow up to and then take a maintainence window and knock the extra rows off.

 

 

 

user451376  

Posts: 5
Registered: 09/15/05

 

  Re: Deleting large number of records
Posted: 14-Apr-2006 01:23   in response to: user451376
 
  Reply

 

Hello again,

Thank you all for your replies. It seems that deleting large amount of data in Oracle is a challenge .

The idea of partitioned table seems to be the best solution. I will implement it.

Thanks again,

Greg

 

 

Jay Munshi  

Posts: 29
Registered: 11/07/05

 

  Re: Deleting large number of records
Posted: 14-Apr-2006 01:27   in response to: user451376
 
  Reply

 

Hello,

Like all above Partitioning is the feature for you. You can just truncate your partition. Truncate does not generate a rollback. Hence, no issues with increasing your retention etc.

regards,
Jay Munshi
http://jaydba.tripod.com

 

 

 

Jay Munshi  

Posts: 29
Registered: 11/07/05

 

  Re: Deleting large number of records
Posted: 14-Apr-2006 01:29   in response to: user451376
 
  Reply

 

And I think you dont have a Problem with Oracle but with your design. 0.6 billion rows and no partitions????

regards,
Jay Munshi
http://jaydba.tripod.com

 

 

user568792  

Posts: 1
Registered: 04/10/07

 

  Re: Deleting large number of records
Posted: 10-Apr-2007 23:35   in response to: user451376
 
  Reply

 

relating to undo table space :

you can omit undo management facility through uncommand the line UNDO_MANAGEMENT=AUTO to #UNDO_MANAGEMENT=AUTO in init.ora or your pfile.

after the change of this parameter data base will be restart must.

delete large number of data from table with the help of TRUNCATE command which is not required any rollback segment or undo table space, because its auto commit process.

if you not habituated with truncate command, then you go to PLSQL procedure syntax and delete one record at a time and commit with the help of cursor.

I think your purpose is solve.

 

 

 

user619216  

Posts: 1
Registered: 02/08/08

 

  Re: Deleting large number of records
Posted: 08-Feb-2008 09:10   in response to: user451376
 
  Reply

 

I’ve been working on different means to delete large amounts of data.

The undo/rollback problem is easily worked around by using a PL/SQL block to open a cursor and delete a number of rows at a time. Just create a loop that deletes one row at a time, and commits every n rows.

It seems a bigger challenge when you want to delete a significant amount of data without disrupting transactions. So far I’m using a shell script to loop a PL/SQL block that deletes n rows and exits, waiting a couple of seconds between each iteration to let waiting transactions get through.

 

 

Tanel Poder  

Posts: 181
Registered: 07/06/98

 

  Re: Deleting large number of records
Posted: 08-Feb-2008 20:59   in response to: user451376
 
  Reply

 

The problem is in deleting oldest records. When I
simply issue a DELETE command I get a message that
undo tablespace is to small, transaction rollback
will not be possible and therefore records cannot be

What is the actual error message text? (the ORA- code?)

deletion. Deleting records in smaller sets, by 100
000 for example, takes too long because indexes must
be updated after every commit.

This is a myth. The indexes are updated during DML execution anyway, regardless of the commit. Deleting 100000 rows at a time would probably not make your delete much slower at all (as the delete CPU & physical IO time will be much more significant than the 17 commits would take).


Tanel Poder
http://blog.tanelpoder.com

 

 

 

Tanel Poder  

Posts: 181
Registered: 07/06/98

 

  Re: Deleting large number of records
Posted: 08-Feb-2008 21:07   in response to: user451376
 
  Reply

 

Note that you need to buy partitioning licenses for Oracle if you already don’t have ’em

 

 

hkchital  

Posts: 4,974
Registered: 11/06/98

 

  Re: Deleting large number of records
Posted: 08-Feb-2008 21:13   in response to: user568792
 
  Reply

 

Re the statement
"you can omit undo management facility"
Undo still has to be managed. If not with AUTO, manually with ROLLBACK SEGMENTs
created by the DBA in [preferred architecture] a dedicated Tablespace.

Re the statement
"delete large number of data from table with the help of TRUNCATE command"
The TRUNCATE command doesn’t delete "large number". It deletes "ALL" records —
every single record in the table. There is no rollback for a Truncate. Use TRUNCATE
only when you are sure that you want really empty the table.

Re the statement
"PLSQL procedure syntax and delete one record at a time and commit with the help of cursor."
With Undo_Retention, undo space requirements can still be slightly high.
However, the major issues with this method are :
a. Increased "log file sync" waits — every commit waits on the LGWR
b. Very likely "fetch across commit" issues resulting in ORA-1555 errors unless you
really know how to write this
c. Increased redo generation because every commit generates it’s own redo record.
This recommendation will SLOW SLOW SLOW the deletes.

 

 

 

user8862826  

Posts: 1
Registered: 03/23/10

 

  Re: Deleting large number of records
Posted: 23-Mar-2010 21:12   in response to: hkchital
 
  Reply

 

Hi All,

I have another Idea .

1. create global temporary table <newTable> as select * from <oldTable> where …
2. truncate <oldTable>
3. alter <oldTable> nologging — If it is logging !
4. insert /*Append*/ into <oldTable> as select * from <newTable>
5. drop <newTable>
6. alter <oldTable> logging — If oldTable was logging mode !

Regards
Wilson
http://www.wilsonshen.idv.tw

 

 


VN:F [1.9.22_1171]
Rating: 0.0/10 (0 votes cast)
VN:F [1.9.22_1171]
Rating: 0 (from 0 votes)

Post a Comment

You must be logged in to post a comment.