2 Replies Latest reply: Jan 31, 2014 5:49 PM by Frank Kulash RSS

    Truncate vs Delete in Loop

    TheOldHag

      I understand that truncate lowers the high water mark on a table which allows space to be freed up and is generally quicker than a delete. However, if you have a temp table in a loop in which you are storing some scratch data each time you go through a loop and that scratch data is a constant size, lets size 500 rows, would it be better to delete the table so as to not have to acquire space each time through the loop. Also, I understand in this situation it may be better to use a nested table in PL/SQL but the question is still a valid one since I don't see it considered much in the discussions as delete retains space that you will end up needing to allocate repeatedly in a loop.

        • 1. Re: Truncate vs Delete in Loop
          Frank Kulash

          Hi,

           

          I'm not sure I understand the question, but I'm pretty sure the answer is TRUNCATE.

          If you want to remove all the rows from a table, and you don't need the option to ROLLBACK, then TRUNCATE TABLE is the fastest way to do it.  Whatever advantages you see in DELETE won't make up for that.

          • 2. Re: Truncate vs Delete in Loop
            rp0428
            I understand that truncate lowers the high water mark on a table which allows space to be freed up and is generally quicker than a delete. However, if you have a temp table in a loop in which you are storing some scratch data each time you go through a loop and that scratch data is a constant size, lets size 500 rows, would it be better to delete the table so as to not have to acquire space each time through the loop. Also, I understand in this situation it may be better to use a nested table in PL/SQL but the question is still a valid one since I don't see it considered much in the discussions as delete retains space that you will end up needing to allocate repeatedly in a loop.

            That would be a typical use case for using the REUSE STORAGE clause of the TRUNCATE command.

             

            The TRUNCATE command, by default, does release all of the allocation but the REUSE clause prevents it from doing that.

             

            Other typical uses of TRUNCATE myTAble REUSE STORAGE are in ETL processes where, as in your use case, a table is reloaded every day (or other time period) with about the same amount of data.

             

            As you pointed out it is much quicker to retain the storage than to deallocate it and then reallocate it.

             

            See the 'STORAGE Clauses' section of the TRUNCATE command in the SQL Language doc

            http://docs.oracle.com/cd/B28359_01/server.111/b28286/statements_10007.htm

            STORAGE Clauses

            The STORAGE clauses let you determine what happens to the space freed by the truncated rows. The DROP STORAGE clause and REUSE STORAGE clause also apply to the space freed by the data deleted from associated indexes.

            DROP STORAGE Specify DROP STORAGE to deallocate all space from the deleted rows from the table except the space allocated by the MINEXTENTS parameter of the table or cluster. This space can subsequently be used by other objects in the tablespace. Oracle Database also sets the NEXT storage parameter to the size of the last extent removed from the segment in the truncation process. This is the default.

            REUSE STORAGE Specify REUSE STORAGE to retain the space from the deleted rows allocated to the table. Storage values are not reset to the values when the table or cluster was created. This space can subsequently be used only by new data in the table or cluster resulting from insert or update operations. This clause leaves storage parameters at their current settings.

            If you have specified more than one free list for the object you are truncating, then the REUSE STORAGE clause also removes any mapping of free lists to instances and resets the high-water mark to the beginning of the first extent.