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

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

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.

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.

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.

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

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

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

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.

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.

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

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.

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