Oracle – TRUNCATE vs DELETE – Spoiler: TRUNCATE Wins & Why? Hours vs Seconds!

Why DELETE is bad?

In Oracle, when it comes to deleting  ** A L L **   the data from a table (clearing the contents of a staging table for example), using a DELETE statement is the most inefficient choice.

Here are some reasons why DELETE is bad

  1. It uses UNDO
  2. It uses REDO
  3. It fires DELETE triggers on the table
  4. It readjusts the indexes as it deletes
  5. It could run for a very long time if the table is huge
  6. ..and one would run into UNDO space related errors eventually on a huge table that most of you hate but are familiar with – ORA-30036: unable to extend segment by 8 in undo tablespace ‘UNDOTBS’
  7. In case the table is one that is actively queried, others may see this error if the DELETE operation takes too long and removes a lot of data – ORA-01555 Snapshot Too Old
  8. High water mark is not reset

Full table scans have to work harder because they have to first find out what constitutes free space within extents.

Enter – TRUNCATE statement:

Truncate is a God-send (not really, but in the manner of speaking) for quickly removing all the contents of a table but don’t jump to that conclusion too quickly because, the options used with TRUNCATE do matter (discussed later).

Here are some good reasons to use TRUNCATE. Let us look at it from a DELETE statement replacement perspective and negate each con from above

    • It uses UNDO

No.

    • It uses REDO

No.

    • It fires DELETE triggers on the table

No.

    • It readjusts the indexes as it deletes

No. It simply resets HWM’s (discussed later)

    • It could run for a very long time if the table is huge

No. It will finish fast with the right TRUNCATE option.

    • ..and one would run into UNDO space related errors eventually on a huge table that most of you hate but are familiar with – ORA-30036: unable to extend segment by 8 in undo tablespace ‘UNDOTBS’

No

    • In case the table is one that is actively queried, others may see this error if the DELETE operation takes too long and removes a lot of data – ORA-01555 Snapshot Too Old

No.

    • High water mark is not reset

No. Truncate resets the high water mark (HWM). What is HWM? It is simply a marker within a segment beyond which there will be no data. So, when Oracle does full table scans on the truncated table, it will simply not scan past HWM.

What does the Tom have to say on it?

This thread in AskTom has an excellent explanation of HWM. It would be hard for anyone to beat that explanation.

TRUNCATE TABLE options

Now, to the options that TRUNCATE can use

  • No option – as used in “TRUNCATE TABLE employees”

This is the same as using the “DROP STORAGE” option.

  • DROP STORAGE – as used in “TRUNCATE TABLE employees DROP STORAGE”

The extents allocated to the object (table as well as indexes) are freed-up and are available for use by other objects.

  • REUSE STORAGE – as used in “TRUNCATE TABLE employees REUSE STORAGE”

The extents allocated to the object still belongs to the object and is not available for use by other objects. However, when new inserts or updates happen to that object that require more space, that space will be used.

  • DROP ALL STORAGE – feature introduced in 11g that drops the segment

DROP STORAGE vs REUSE STORAGE time difference – 2 hours vs 20 seconds

The answer is “Very significant difference”. How about 2 hours plus vs. 20 seconds in an extreme case?

Recently, I tried to TRUNCATE a table that was taking up about 12 GB of space. When I tried it with no option, which implicitly did “DROP STORAGE”, it took 2 hours and the statement had still not come back. I was startled since, all of my TRUNCATES prior have taken all but a few seconds at the maximum.

I tried the same TRUNCATE with “REUSE STORAGE” option and it completed in 20 seconds!

*** W A R N I N G ***: TRUNCATE is DDL, not DML

Note that TRUNCATE is not a DML but a DDL. For folks coming from the SQL Server world, that is a surprise. The difference in Oracle is that DDL’s COMMIT the transactions that they are part of. So, if you issue a TRUNCATE in the middle of a transaction, you essentially commited it! You cannot ROLLBACK. That is a done deal at that point.

Other Considerations

There are a lot of factors that influence the time it takes but REUSE STORAGE is certainly faster. One does have to understand the drawbacks of using this option though. The space is still there and it belongs to the object on which TRUNCATE was done and it will be re-used later. Oracle would have to hunt and peck for the free space left behind by the REUSE option. If someone came along and happily did a INSERT INTO /*+ APPEND */, the free space that is dispersed, will end-up getting wasted as the APPEND hint will force the insert to allocate space beyond the HWM.

There is no silver bullet. The choice of the right option depends on your situation. Please keep “storage wastage” in mind when continually using TRUNCATE with REUSE option followed by /*+ APPEND */ based insert (especially if the underlying tablespace is shared by a lot of objects). Your DBA may pay you a visit.

Side note:

I wish there was a way to monitor the progress of a truncate in Oracle. Session longops is not updated for TRUNCATE’s. The SQL being executed is mostly queries about blocks and the dba_extents and dba_segments views are not reflecting the changes that are happening underneath. If you know of a way to monitor TRUNCATE’s please let me know. Google did not help!

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s