Efficient way to empty tables in ORACLE

To delete all the records of a table you have some options, each one has its differences, in terms of performance, I think this would be the order of best to worst.

TRUNCATE

• It is a command DDL
• Delete all records
• Triggers are not executed
• It is not possible to make a ROLLBACK
• Does not affect the dependencies of the table: indexes, permissions or triggers

DROP TABLE

• It is a command DDL
• Deletes all records together with the dependencies of the table: permissions, triggers, indexes, etc.
• Triggers are not executed
• It is not possible to make a ROLLBACK
• Forces us to recreate the table next to its dependencies.

DELETE without WHERE

• It's a command DML
• Delete all records
• It is possible to perform a ROLLBACK of the operation
• Eventually the associated trigger is executed

More detail of the differences you will be able to find in this article.

PS: I am very impressed by your comment about WHERE 1=1 , I can not find a reason for the query optimizer to make a difference only with that filter. On the other hand the time it takes a query is not a good indicator most of the time, depending on the workload of the server the same operation can vary a lot, it is preferable to see things such as number of readings / writes.

TRUNCATE is much faster than DELETE. Only with TRUNCATE you will delete all what is in the table without any distinction.

If at any time you need to control what to eliminate you will have to use DELETE.