CODEWITHSUNDEEP
sqloracle-databaseoracle11gparallel-processing
DjBuddy
DjBuddy

Reputation: 181

Want to enable PARALLEL and NOLOGGING on Oracle Delete statement

I have a very huge table with more than 3.5 billion records that increases 500K each day, I want to delete all records before year 2014. Every delete command I run, after few hours fall into error state, so I am looking at doing this job faster, last command I run was: 

DELETE /*+ PARALLEL (8) */ FROM XYZ WHERE YEAR <= 2014;

After 744 minutes I get

ORA-12801: error signaled in parallel query server

I guess if I could run the DELETE command with both PARALLEL and NOLOGGING, switch maybe I could do this, but I don't know how to run the session in NOLOGGING state and at the same time enable PARALLEL for the command, also I know we can run

DELETE /*+ NOLOGGING PARALLEL (8) */ FROM XYZ WHERE YEAR <= 2014;

But as I find in somewhere, it seems that with this command PARALLEL hint will be ignored.

Please advise on how to run the DELETE command both in PARALLEL and NOLOGGING

Upvotes: 0

Views: 19066

Answers (1)

Florin Ghita
Florin Ghita

Reputation: 17643

Not logging the delete operation won't help too much. There is really small amount of things to be redone for a delete operation.

The problem here is a huge amount of undo(It contains every row deleted in order to be inserted in case of error/rollback). Also, parallel just speed up things, don't change the amount of undo.

I see here two solutions/workarounds:

  1. increase the undo tablespace.(talk with your dba)

  2. delete in smaller chunks.(for example, delete 4 months, commit, then again 4 months, commit again, etc.)

UPDATE: Now I'm a little unsure about my statement above about redo. Because writing in undo will generate redo. However, the longest part of the execution of your delete is the rollback because you hit an exception(it may be 2/3 of the time). Splitting the task in 3-4 chunks may be faster than your failed query.

Upvotes: 1

Related Questions