13 Replies Latest reply on Apr 15, 2008 3:03 PM by 575729

    Why is TRUNCATE DDL and not DML?

    557676
      Hi,

      When a TRUNCATE Table statement is executed the data in the table gets deleted(also memory is deallocated).It implies that the Truncate statement affects the data.Then why is it categorized under DDL statements.

      So,Why is a TRUNCATE DDL and not DML?

      Thanks in advance.
        • 1. Re: Why is TRUNCATE DDL and not DML?
          ushitaki
          So,Why is a TRUNCATE DDL and not DML?
          The trucate table lead to commit, on this point it is DDL is not DML.
          Same as that DROP TABLE is DDL is not DML.
          The truacate table is like a DROP TABLE but reserving definition.

          And the truncate table can not be specified WHERE clsuse,
          this is different from delete statement.
          • 2. Re: Why is TRUNCATE DDL and not DML?
            Nicolas.Gasparotto
            Truncate statement affects the data
            Not only, that affect the object as well. Check the LAST_DDL_TIME on USER_OBJECTS after truncating your table.
            TRUNCATE will commit, and it's not rollbackable. That change the storage definition of the object. That's why it is a DDL.

            Nicolas.
            • 3. Re: Why is TRUNCATE DDL and not DML?
              BluShadow
              It's DDL because it isn't deleting records per se as you get with DML statements, but rather altering the structure of the table by moving the internal high water mark back to the start.
              • 4. Re: Why is TRUNCATE DDL and not DML?
                John Spencer
                My understanding is that internally, it is implemented more like a DROP/CREATE.
                SQL> CREATE TABLE t AS
                  2  SELECT rownum id, TO_CHAR(TO_DATE(rownum, 'j'), 'Jsp') descr
                  3  FROM all_objects
                  4  WHERE rownum < 501;

                Table created.

                SQL> SELECT object_id, data_object_id, created, last_ddl_time
                  2  FROM user_objects
                  3  WHERE object_name = 'T';

                OBJECT_ID DATA_OBJECT_ID CREATED              LAST_DDL_TIME
                ---------- -------------- -------------------- --------------------
                     54638          54638 11-apr-2008 10:18:37 11-apr-2008 10:18:37

                SQL> TRUNCATE TABLE t;

                Table truncated.

                SQL> SELECT object_id, data_object_id, created, last_ddl_time
                  2  FROM user_objects
                  3  WHERE object_name = 'T';

                OBJECT_ID DATA_OBJECT_ID CREATED              LAST_DDL_TIME
                ---------- -------------- -------------------- --------------------
                     54638          54639 11-apr-2008 10:18:37 11-apr-2008 10:19:29
                The object_id column is defined in the documentation as "Dictionary object number of the object" and the data_object_id column is "Dictionary object number of the segment that contains the object". So the segment id changes on truncate.

                Interestingly, at least in newer versions, Oracle seems to be able to avoid doing anything if the table you try to truncate already has the high water mark at the beginning.

                John
                • 5. Re: Why is TRUNCATE DDL and not DML?
                  519688
                  the SQL Reference manual states that DML statements "do not implicitly commit the current transaction", but that "DDL Oracle Database implicitly commits the current transaction before and after every DDL statement."

                  Additionally, the Concepts manual states "The execution of DDL statements differs from the execution of DML statements and queries, because the success of a DDL statement requires write access to the data dictionary"

                  Truncate commits, and it modifies the data dictionary, therefore it is DDL, not DML.
                  • 6. Re: Why is TRUNCATE DDL and not DML?
                    626804
                    Nice point John..
                    Good to know that
                    • 7. Re: Why is TRUNCATE DDL and not DML?
                      BluShadow
                      Truncate commits, and it modifies the data
                      dictionary, therefore it is DDL, not DML.
                      Whilst the statement about it updating the Data Dictionary qualifies it as DDL, it is wrong to say that because it commits it is DDL. The truth there is that because it is DDL it does the commit, not the other way around.

                      ;)
                      • 8. Re: Why is TRUNCATE DDL and not DML?
                        519688
                        The truth there is that because it is DDL it does the commit, not the other way around.
                        not sure about that (I think it's one of those things, that until oracle issues a press release, is up for debate).

                        anyway, my point was that I was only able to find 2 specific statements regarding the difference between DML and DDL (implicit commit and writing to the data dictionary), and seeing how TRUNCATE does both of those things, it clearly must be a DDL. whether it commits because it wants to, and therefore is defined as a DDL, or it wants to be a DDL and therefore must do a commit, is the chicken-egg argument.

                        of course, if someone wants to scour the doc better than I have, or find a white paper somewhere, and decide the chicken-egg argument, I mean commit-ddl argument, I'll be happy to accept the verdict of the judges.
                        • 9. Re: Why is TRUNCATE DDL and not DML?
                          Sven W.
                          Think your table as a tree full of apples.

                          A delete would pluck all apples from the tree. This can still be considered gardening.

                          A Truncate would chop the tree right at the root. Much faster when using a good chainsaw but also completely destroys the tree. Not gardening anymore, but heavyweight timbersports!

                          The result is that there are no more apples on this tree. However the delete operating keeps some structure (trunc and branches). Even if that is not needed anymore.
                          • 10. Re: Why is TRUNCATE DDL and not DML?
                            519688
                            what about "truncate reuse storage"? that also keeps the branches (if I understand your analogy correctly, which I might not).
                            • 11. Re: Why is TRUNCATE DDL and not DML?
                              Sven W.
                              Hm... that could be considered building a fence around the tree before chopping it down. The space insidfe the fence will be reserved for the replacement tree. No other tree is allowed inside that reserved space.
                              • 12. Re: Why is TRUNCATE DDL and not DML?
                                519688
                                like I said, I didn't think that I correctly understood your analogy ;)
                                • 13. Re: Why is TRUNCATE DDL and not DML?
                                  575729
                                  There are many other difference between delete and truncate.

                                  At server level a delete statement create redo logs as well as rollback segaments.
                                  a truncate does create some redo log ( i think it from data dictionary) but no rollback data.

                                  another important thing about the delete vs truncate is the way they execute. a delete statement must have to use database cache it brings data into cache, perform delete operations on it

                                  A truncate statement deals at extent level. It does not deletes the data from the extents or segaments instead it makes some changes in extent map and move the high water mark to the begining of the file.

                                  I found this question quiet often on this forum and also i myself want to check the santity of these concepts.