John WatsonMessages: 7255Registered: January 2010 Location: Global Village

Senior Member

manikandan23 wrote on Sun, 26 February 2017 23:05

Thank you guys and any other suggestions?

The table is independent table.

You could avoid the delete completely and instead create a VPD policy that would stop the rows being visible. A kind of virtual delete.
However, until you define "best" I don't see how you can evaluate the options.

What version of the database do you have. And what is your criteria for deleting. Your table sounds like it would be a good candidate for being partitioned. Dropping a partition is very quick and in 12c it even handles global indexes by ignoring the rows pointing to the dropped partition until the database gets around to cleaning up the global index.