Interviewing: The difference between Delete and Truncate in Oracle

I interviewed a couple of senior ETL developer candidates last week, so I shook the dust off of some of my tried-and-true probes for technical knowledge.

I’ve always had a liking for asking for the differences between “truncate” and “delete” ever since I was asked it myself some years ago. I think it allows candidates to demonstrate a nice range of knowledge.

Here are some answers in no particular order.

  1. “Truncate is fast, delete is slow”. Hmmm, well that’s mostly true I guess. The most basic or all answers.
  2. “Truncate is DDL, delete is DML”. Is this just so obvious that people don’t mention it?
  3. “You can’t rollback a truncate”. Indeed. See 2 above, of course. And 1.
  4. “Truncate is implicitly a commit”. A better answer than 3 I think. I seem to recall that someone once mentioned that there are actually two commits in a truncate, one before and one after … but maybe I dreamed that. I should test it really.
  5. “You can’t grant permission to truncate a table”. Ah, practical experience shines through. If you don’t like your ETL process to connect as the owner of the schema then this is a challenge that has to be overcome with stored procedures or something sophisticated like that. You really don’t want to grant “DROP ANY TABLE” to your ETL user.
  6. “You can delete any subset of rows, but you can only truncate the complete table, or a partition or subpartition of it”. Is this also so obvious that nobody mentions it?
  7. “Truncate makes unusable indexes usable again”. A real gotcha for the unwary. If you attempt to optimise a data load by rendering indexes unusable and truncating a table (possibly followed by an index rebuild and a partition exchange) then be careful of the order.
  8. “Truncate can’t maintain foreign keys”. It’s “cascading delete”, not “cascading truncate”. That would be an interesting feature though — point 4 above would make it a little trickier. Truncating an index cluster is pretty close to a “cascading truncate” to a limited extent though. In any case no truncate is permitted on a table referenced by foreign keys.
  9. “You can’t flashback a truncate”. This is an oddity to me. We can flashback a “drop table”, rollback uncommited deletes, or use flashback to recover pre-commit deleted data, but a truncate is a barrier across which we cannot flashback.
  10. “Truncate deallocates space, delete doesn’t”. Unless you want it not to, using the “reuse storage” clause. However the high water mark is reset in either case so maybe that’s a better answer …
  11. “Truncate resets the high water mark, delete doesn’t”. And on the indexes, also. See 7 above also.
  12. “Truncate allows special treatment of materialized view logs”. More of that practical experience and/or documentation-reading skill shows through.
  13. “DML triggers do not fire on a truncate”. Because … um … it’s DDL not DML.

I suppose there are other issues as well, but this shows that it’s a complex business.  I really wish we could grant “Truncate table” to a user or role … enhancement request time I think.

Isn’t it interesting that in some circumstances only one method can be used to remove all rows from a table in a single step, sometimes both methods can. In other circumstances both methods can remove a subset of rows from a table, and sometimes both can.

Oh well, back to work.

About these ads

26 thoughts on “Interviewing: The difference between Delete and Truncate in Oracle

  1. Hi again David – good to see you posting again.

    truncate also discards an objects segments and re-allocates a new one.

    You can see this by watching the value of user_objects.data_obj_id change after each truncate ( user_objects.data_obj_id – the object_id of the segment that contains the object)

    This is part of the reason why truncate can be so expensive to carry out in a high transaction rate system.

    Best Regards,

    Mathew Butler

  2. Yes indeed — although if you have never inserted any rows into the table then a truncate doesn’t change the data object id (in 10g at least). Even a single insert that is then rolled back causes a data object id change on truncate.

    I suppose the usual reason people would be truncating in an OLTP system would be to do with temporary table usage, or something like that, where a global temporary table (or subquery factoring clause) would often do the job just as well.

  3. David:

    Point 3 and others…

    I assume this is for Oracle. I recall I did undo on a truncate table on SQLServer 2005 just a few months ago…

    Cheers.

    Carlos.

  4. Oh yes — I’m exclusively an Oracle wonk. Maybe I should change the title …

    I remember that when a company I worked for a long time ago changed from Ingres to Oracle there was much hilarity when we discovered that a truncate couldn’t be rolled back.

  5. Dom’s point is good. Had a major performance issue in some code which had a loop including a truncate ‘because it is faster than a delete’. All the cached SQL was invalidated (because the table was subjected to DDL) and required re-parsing. Man, that code ran SLOW.

  6. Reposting for a hopefully readable comment.

    Another issue recently discussed here is that truncate doesn’t reset statistics for the table/indexes. It’d be nice if a clause such as “reset statistics” will be added for the “truncate table” command.

  7. Yes, that’s true. As you say, a handy option rather than something you’d want applied by default.

    It’s not a difference per se, but is an area of commonality between delete and truncate. In fact there’s an explicit commonality as both operations are logged as table modifications if the table has the “monitoring” flag set against it.

  8. David did you see this?

    http://www.freelists.org/archives/oracle-l/03-2008/msg00493.html

    Very funny!

    ***********************************************

    In this scenario you just truncated a table in production that you
    meant to truncate in development. Now what do you do?

    1) Shout OHHH SH**! (fill in the ‘*’ with whatever letters you think
    are appropriate)

    2) After you’ve gotten over the initial shock of this monumental mistake,
    do a

    Shutdown immediate;

    3) Stop, think and immediately tell your manager or coworkers what you did.

    4) Curse a coworker who had nothing to do with YOUR mistake. It won’t
    help the situation, but now you can feel bad for two stupid mistakes.

    5) Apologize for cursing your coworker and accept FULL responsibility
    for YOUR mistake.

    6) Now that you are rational, calmly discuss with your
    manager/coworkers the best course of action. If it is decided to
    proceed with a point-in-time recovery, then continue with the
    following steps.

    7) Backup the controlfile in case you have to restart the recovery:

    Alter database backup controlfile to
    ‘$ORACLE_HOME/dbs/$ORACLE_SID_backup.ctl’;

    8) Check the alert log for the exact time you started the “shutdown
    immediate”.

    9) Think about how much time may have passed between step 1 and 2,
    then decide on a safe point-in-time before the truncate occurred.

    10) Run ‘rman’.

    RMAN> connect target /
    RMAN> restore database;
    — After restore completes
    RMAN> exit

    11) Run ‘sqlplus “/ as sysdba’

    SQL> recover database until time ‘YYYY-MM-DD:HH24:MI:SS’;
    SQL> alter database open resetlogs;
    –shutdown may take a while to apply undo from recovery.
    SQL> shutdown immediate;
    SQL> Startup restrict;

    12) Check to make sure everything looks OK.

    13) If everything looks good, then open the database to the users:

    SQL> shutdown immediate;
    SQL> startup;

    14) Thank your coworkers for all their help and be grateful you still
    have a job.

  9. Always easy to be wise in hindsight, but preventing an accidental truncation is sometimes just as easy as disabling table locks as long as you don’t need table locks in your regular application processing.

  10. > you know if that’s documented anywhere?
    I’ve observed the behaviour, not sure I’ve read it definitively.

    Just did a quick search of various sources.

    They are hardly the most convincing of articles – their style and spelling and grammatical mistakes detracting from their authority, but from Metalink note 62143.1 “Understanding and Tuning the Shared Pool”:


    Avoid Invalidations

    Some specific orders will change the state of cursors to INVALIDATE. These orders modify directly the context of related objects associated
    with cursors. That’s orders are TRUNCATE, ANALYZE or DBMS_STATS.GATHER_XXX on tables or indexes, grants changes on underlying objects. The associated cursors will stay in the SQLAREA but when it will be reference next time, it should be reloaded and reparsed fully, so the global performance will be impacted.

    and also metalink note 123214.1 – “Truncate – Causes Invalidations in the LIBRARY CACHE”.

  11. It’s an interesting feature for sure. It makes sense in some ways, but since a truncate is not going to change the table statistics one also wonders why it’s there. Maybe the truncate command ought to come with a “no invalidate” option, like dbms_stats gathering procedures.

  12. My interpretation:

    The change in data object id signifies that the objects segments have been discarded. So, although the objects stats have not been affected – they would no longer reflect the data stored in the table. And in some senses this is really a new object ( as far as cached cursors are concerned ).

    I expect that the optimizer avoids making any assumptions about the state of the object before the truncate by invalidating referencing cursors.

  13. Given this some more thought…

    I think it has nothing to do with the table stats. It’s purely that truncate is DDL, and I hypothesise that the optimizer just doesn’t know that kind of DDL and so has to re-parse cursors to ensure that the table can still support the cached query.

  14. Hi David,

    I’ve seen you publish some lists on your blog in the past, so I was wondering if you have a list of interview questions you have asked ETL developers? I’m trying to come up with a list that is not too tool-specific (we use DataStage ourselves), and I’m debating if it should be Oracle-heavy. Thoughts on that?

    Thanks.

    Kashif

  15. So if the truncate syntax goes like
    TRUNCATE { TABLE [ schema. ]table
    [ { PRESERVE | PURGE } MATERIALIZED VIEW LOG ]
    | CLUSTER [ schema. ]cluster
    }
    [ { DROP | REUSE } STORAGE

    Where it is really not mandatory to add (DROP STORAGE) to the truncate statement knowing fully well the Watermark is dropped. Lately some of my Truncate statements that are part of Plsql package have been failing randomly with a ORA-03291 error. I iteratively walk through a list to truncate some tables and 1 or 2 of 10 randomly fail. But work fine when I rerun. Its been a night mare for couple of weeks now. Any thoughts please.

    ORA-03291: Invalid truncate option – missing STORAGE keyword
    ORA-06512: at “xxxx.xxxxxxxxx”, line 35
    ORA-06512: at line 2

    Thanks

    • @Jatin,

      I have no problem with people putting in links to other genuinely helpful resources, but you’re referencing as “better and easier” a blog post which:

      i) is just a five-point list of differences.

      ii) does not reference which RDBMS it relates to.

      iii) is probably wrong in respect of rollbacks and triggers.

  16. TRUNCATE is a DDL (data definition language) command whereas DELETE is a DML (data manipulation language) command.

    You can use WHERE clause(conditions) with DELETE but you can’t use WHERE clause with TRUNCATE .

  17. Truncate an Delete both are used to delete data from the table. These both command will only delete data of the specified table, they cannot remove the whole table data structure.Both statements delete the data from the table not the structure of the table.

    TRUNCATE is a DDL (data definition language) command whereas DELETE is a DML (data manipulation language) command.

    You can use WHERE clause(conditions) with DELETE but you can’t use WHERE clause with TRUNCATE .

    You cann’t rollback data in TRUNCATE but in DELETE you can rollback data.TRUNCATE removes(delete) the record permanently.

    A trigger doesn’t get fired in case of TRUNCATE whereas Triggers get fired in DELETE command.

    If tables which are referenced by one or more FOREIGN KEY constraints then TRUNCATE will not work.

    TRUNCATE resets the Identity counter if there is any identity column present in the table where delete not resets the identity counter.

    Delete and Truncate both are logged operation.But DELETE is a logged operation on a per row basis and TRUNCATE logs the deallocation of the data pages in which the data exists.

    TRUNCATE is faster than DELETE.

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