Oracle 10g: Truncate before drop. Any real improvement?

Su Oracle, effettuare una TRUNCATE prima del DROP è effettivamente conveniente?(in velocità e consumo di risorse)

Comportamento di TRUNCATE e DROP:

  • TRUNCATE:
    • TRUNCATE removes all rows from a table
    •  TRUNCATE statement is a DLL command and cannot be rolled back  and no triggers will be fired=> generates no rollback data
    • On a clustered table, the data must be removed from the entire cluster, not just the one table. Any referential integrity constraints on a table must be disabled before it can be truncated.
    • The database does not actually delete any rows with the Oracle TRUNCATE statement, but with the Oracle TRUNCATE resets the table definition to an empty table by resetting the high water mark. As the truncated table begins to grow, the space where the old rows were written will be overwritten with the new rows.
  • DROP
    • The DROP command removes a table from the database. All the tables’ rows, indexes and privileges will also be removed.
    • DROP statement is a DLL command, No DML triggers will be fired. The operation cannot be rolled back => generates no rollback data
    • Oracle 10g introduced the recycle bin. You can recover a table that you have dropped from the Oracle recycle bin by using the flashback table command.
What about e High Water Mark?
The High Water Mark is the maximum number of blocks which have ever contained data. Deleting records from a table frees up the space but does not move the HWM. In order to reset the High 
Water Mark we have to drop and recreate the table, or just truncate it.
Normally we wouldn't bother. However, the High Water Mark defines the range of a full table
scan. So if we have a table which once contained a lot of records but no longer does so and
also is subject to frequent full table scans then perhaps reseting the HWM would be a good use of our time.

Ma se la tabella deve essere cancellata, non ha senso preoccuparsi dell'HWM.

In definitiva, non ci sono vantaggi ad effettuare una TRUNCATE prima della DROP, perché in effetti le due operazioni hanno le stesse caratteristiche in termini di consumo di risorse. L’unica differenza (dalla versione 10g) è il recycle bin, che occupa effettivamente spazio utile, per by-passarlo basta aggiungere l’opzione PURGE alla DROP:

drop table tbl_rc purge;

drop_table

Riferimenti:
http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:595970200346389228
http://www.orafaq.com/faq/difference_between_truncate_delete_and_drop_commands
http://www.dba-oracle.com/t_oracle_recycle_bin.htm
http://docs.oracle.com/cd/B28359_01/server.111/b28286/statements_9003.htm
http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:669044239081
http://chandu208.blogspot.it/2012/02/high-water-mark-hwm.html
Annunci