Diff B/w Delete & Truncate in Oracle DB

More Details on Our Website

On bigger picture they serve the same purpose but there are many Differences listed with examples  

Point	Delete	Truncate
1.  Data Recovery	Delete: Come under the DML Category, we need to commit or Rollback explicitly to make the changes permanent, so we can recover the data by Rollback command fully with in a session or up to a point if Save Points are used See listing 1 for more details	Fall In DDL Category (DDL Command issue the Auto commit implicitly) so no chances of Recovery even not using the Flashback table method. But Truncate operations are also logged , they didn’t generate redo SQL but they are logged , view for truncated data info V$LOGMNR_CONTENTS  .More details on link http://www.thatjeffsmith.com/archive/2011/07/truncates-are-logged/
2. Data Removal	Delete Can remove all or selected data using the Where Clause predicates. Or we can say delete any subset of rows	We can Truncate complete table or a partition or sub partition of a table. See listing 1 for more details
3. Speed	Delete is Slower because oracle maintain the redo logs for Read Consistency (so that every session connected can see a consistent data at a given point of time ) Delete is very time consuming activity especially when table have numerous indexes and Triggers associated with table	Faster as no data logs are maintained no associated trigger firing.
4. DML Triggers Firing	DML (Delete) triggers associated with table will fire.	DML Trigger will not fire in case of truncate method. See Listing 2 for more details.
5. Flashback Technology	Data can be recovered even after commit operation using Flashback Table options Flashback_transaction_query table will give what to recover and up to which point.	Data cannot be recovered in truncate method by Flashback table option. See Listing 3 for more details
6. Referential Integrity Constraint Behavior	if we don’t have related data in child table then we can delete the data from the parent table or we have variants like On Delete Cascade & on Delete set Null. For more details see listing 4	We can’t truncate a table with enable Referential Integrity Constraint, even there is no data in the child table, we have to disable or drop the constraint if we want to truncate the table. Exception: Truncate is possible if the FK is self-referential means primary key and foreign key are on the same table.
7. Space De allocation or Space Utilization	No extent reset with delete when deleting rows from a table, extents are not de allocated, So if there were 50 extents in the table before the deletion, there will still be 50 after the deletion. See Listing 5 & 6 for more details	Truncate: When a table is truncated it will free the space allocated except in case of reuse storage clause. This space can subsequently be used only by new data in the table or cluster resulting from insert or update operations .All extents are de allocated leaving only the extents specified when the table was originally created  .Example So if the table was originally created with min extents 3, there will be 3 extents remaining when the tables is truncated. When you truncate a table, NEXT is automatically reset to the last extent deleted.
8. High Water Mark	Delete will not reset the high water mark See Listing 7 for more details.	Truncate will reset the High Water mark which is very important for performance point of view as in case of full table scan and full index scan oracle will read all the block under high water mark this makes a lot of difference in terms of performance.
9. Cluster	No as such restriction with delete. See Listing 8 for More Details	You cannot individually truncate a table that is part of a cluster. You must truncate the cluster, Delete all rows from the table, or drop and re-create the table.
10. Information Capturing	Delete : we can capture the row information what we have deleted using Delete Method, f you are deleting multiple records then use composite data types (collections & records)	Truncate Don’t have this feature of capturing the deleted records. See Listing 9
11. Function Based Index Impact	DELETE You cannot delete rows from a table if a function-based index on the table has become invalid. You must first validate the function-based index.	Truncate: No as such restriction
12. UNUSABLE Indexes	Delete no as such feature.	Truncate if table is not empty then truncate make all unusable indexes to useable.
13. Complex views	You cannot delete data from a Complex view except through INSTEAD OF triggers. But we can delete data from simple Views and MV.	We cannot truncate a view simple or complex but you can truncate MV with special Features like Preserve MV Logs and Purge MV Logs.
14. Privileges	Delete You need to provide delete table privilege on object.	Truncate you must have drop table privilege there is no truncate table privilege exists.
15. Domain Index	No as such restriction	You cannot truncate the object having domain index in invalid or In progress state

Listing 1 Delete, Truncate, Rollback & Partitions Test

-- Create partitioned table

SQL>create table df (id number, name varchar2 (100))

Partition by range (id) (Partition a1 values less than (10000),

 Partition a2 values less than (maxvalue))

Table created.

-- Insert 100000 records for testing

SQL> begin

    for i in 1..100000 loop

insert into df values (i,chr(i));

 end loop;

     commit;

     end;

   /

PL/SQL procedure successfully completed.

SQL> select count (*) from df

COUNT(*) 

    100000  

-- Check records in each partition                                                                                      

SQL> select count(*) from df partition (a1)

COUNT(*) 

      9999                                                                                          

SQL>  select count(*) from df partition (a2);

COUNT(*) 

      90001

-- Delete all the data                                                                                          

SQL> delete from df

100000 rows deleted.

SQL> select count(*) from df ;

  COUNT(*)                                                                                         

----------                                                                                          

         0   

-- Rollback as deleted data can be rollback.                                                                                     

SQL> rollback;

Rollback complete.

-- delete from individual partition

SQL> delete from df PARTITION (a1);

19999 rows deleted.

SQL> select count(*) from df ;

  COUNT(*)                                                                                         

----------                                                                                         

     80001                                                                                         

SQL> rollback;

Rollback complete.

SQL> select count(*) from df;

  COUNT(*)                                                                                          

----------                                                                                         

    100000   

-- Truncate partition to remove the selected data                                                                                       

SQL> alter table df truncate partition a1 ;

Table altered.

--Check record from truncated partition

SQL> select count(*) from df partition (a1) ;

  COUNT(*)                                                                                         

----------                                                                                         

         0  

--Check record from non truncated partition

              SQL> select count(*) from df partition (a2);

  COUNT(*)                                                                                          

----------                                                                                         

        90001     

          **** END of Listing 1 **** 

Listing 2. DML triggers fire in case of delete not in Truncate

DML Delete triggers fire for Delete not for Truncate.

SQL> CREATE TABLE MY_NAME (FIRST_NAME VARCHAR2 (10),LAST_NAME VARCHAR2 (10));

Table created.

-- Table for error logging

SQL> CREATE TABLE ERR (MSG VARCHAR2(109));

Table created.

--Trigger on Delete event for each row

SQL>  CREATE OR REPLACE TRIGGER DIFFD

   BEFORE DELETE ON MY_NAME

For each row

    BEGIN

    INSERT INTO ERR VALUES ('LOG FIRSTTHEN DELETE');

   END;

    /

Trigger created.

-- Insert data for testing in loop

SQL> BEGIN

    FOR I IN 1..10 LOOP

    INSERT INTO MY_NAME VALUES ('ARUN'||I,'BHARAT'||I);

    END LOOP;

    COMMIT;

    END;

    /

PL/SQL procedure successfully completed.

--Delete from my_name table ..to test firing of trigger

SQL> DELETE FROM MY_NAME ;

10 rows deleted.

-- data comes into the err table as result of trigger firing on MY_Name table

SQL> SELECT count(*) FROM ERR

Count(*)

----------------

10

SQL> ROLLBACK

Rollback complete.

SQL> SELECT count(*) FROM MY_NAME

Count(*)

-------

10

SQL> SELECT count(*) FROM ERR;

Count(*)

-------

0

-- Now truncate the table , no trigger fired here

SQL> TRUNCATE TABLE MY_NAME ;

Table truncated.

SQL> SELECT count(*) FROM MY_NAME

Count(*)

-------

0

SQL> SELECT count( *) FROM ERR

  Count(*)

-------

0

              **** END of Listing 2 ****

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

Listing 3. Referential Integrity Constraints behavior with delete & Truncate

With example of Self referential Integrity constraint

-- Master table

  1* CREATE TABLE MASTER (ID NUMBER PRIMARY KEY ,NAME VARCHAR2(10));

Table created.

-- Detail table

   CREATE TABLE DETAIL (ID NUMBER , VID NUMBER);

Table created.

-- Foreign Key Constraint added

SQL> ALTER TABLE DETAIL ADD CONSTRAINT FK_MM FOREIGN KEY (ID) REFERENCES MASTER(ID);

Table altered.

-- Truncate master table, it can’t because referential integrity constraint is enable for truncate you have to disable the constraint or drop the constraint.

SQL> TRUNCATE TABLE MASTER ;

TRUNCATE TABLE MASTER * ERROR at line 1:

ORA-02266: unique/primary keys in table referenced by enabled foreign keys

--insert data in master table

SQL> INSERT INTO MASTER VALUES (1,'ARUN');

1 row created.

SQL> INSERT INTO DETAIL VALUES (1,2);

1 row created.

SQL> INSERT INTO DETAIL VALUES (1,5);

1 row created.

SQL> DELETE FROM MASTER ;

DELETE FROM MASTER ERROR at line 1:

ORA-02292: integrity constraint (SCOTT.FK_MM) violated - child record found

SQL> DELETE FROM DETAIL;

2 rows deleted.

--now dependency over we can delete now

SQL> DELETE FROM MASTER;

1 row deleted.

--Exception with Truncate we can truncate if referential Integrity constraint is self referential (foreign key and primary key on same table)

-- table with Self Integrity Constraint

create table my_emp_test (empid number,name varchar2(10),manager_id number );

Table created

--Primary Key Constraint

alter table my_emp_test add constraint my_pk primary key(empid);

Table altered

--Foreign Key Constraint

alter table my_emp_test add constraint  my_fk foreign key(manager_id) references my_emp_test(empid);

Table altered

--insert data in table

insert into my_emp_test  values(100,'arun',null);

1 row created

insert into my_emp_test  values(200,'arun',100);

1 row created

insert into my_emp_test  values(300,'arun',200);

1 row created

insert into my_emp_test  values(400,'arun',100);

1 row created

--we can truncate table with enable self referential integrity constraint

Truncate table my_emp_test;

Table truncated.

            **** END of Listing 3 ****

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

Listing 4. Flashback Method

We can recover deleted data after commit by Flashback method but not truncated data

SQL> create table flash (id number , name varchar2(10));

Table created.

SQL> insert into flash values (1,'ar');

1 row created.

SQL> insert into flash values (2,'bh');

1 row created.

SQL> select * from flash

ID   NAME

--- -----

1    ar

2    bh

SQL> commit;

Commit complete.

SQL> SQL> delete from flash ;

2 rows deleted.

SQL> commit;

Commit complete.

SQL> select * from flash ;

no rows selected

  1 SQL> select current_scn from v$database

CURRENT_SCN

-----------

5701428

SQL> flashback table flash to scn 5701428

flashback table flash to scn 5701428                *

ERROR at line 1:

ORA-08189: cannot flashback the table because row movement is not enabled

SQL> alter table flash enable row movement;

Table altered.

SQL> flashback table flash to scn 5701428

  Flashback complete.

SQL> select * from flash

no rows selected

SQL>  flashback table flash to scn 5701342

Flashback complete.

SQL>  select * from flash

ID  NAME

---- ------

1    ar

2    bh

   SQL> truncate table flash;

Table truncated.

We cannot flashback the truncated data.

 **** END of Listing 4 ****

Listing 5. Space De allocation Test

SQL> create table arun_t (id number);

Table created.

SQL>  select extent_id, bytes, blocks
   from user_extents
   where segment_name = 'ARUN_T';

EXTENT_ID     BYTES    BLOCKS

---

        0     65536         8

-- Insert data for testing

SQL> begin
   for i in 1..100000
     loop
    insert into arun_t values (i);
   end loop;

commit;
 end;

/

PL/SQL procedure successfully completed.

n  Now check for the space

n  SQL> select extent_id, bytes, blocks
  2  from user_extents
  3  where segment_name = 'ARUN_T';

EXTENT_ID     BYTES    BLOCKS

        0     65536         8

        1     65536         8

        2     65536         8

        3     65536         8

        4     65536         8

        5     65536         8

        6     65536         8

        7     65536         8

        8     65536         8

        9     65536         8

       10     65536         8

       11     65536         8

       12     65536         8

       13     65536         8

       14     65536         8

       15     65536         8

       16   1048576       128

17 rows selected.

Now i just delete alls record from table test and commit,
delete will not shrink the table and indexes to the original size when table was
created without records nor it will reset the high water mark.

SQL> delete from test;

100000 rows deleted.

SQL> commit;

Commit complete.
SQL> select extent_id, bytes, blocks
  2  from user_extents
  3  where segment_name = 'TEST';
EXTENT_ID     BYTES    BLOCKS

        0     65536         8

        1     65536         8

        2     65536         8

        3     65536         8

        4     65536         8

        5     65536         8

        6     65536         8

        7     65536         8

        8     65536         8

        9     65536         8

       10     65536         8

       11     65536         8

       12     65536         8

       13     65536         8

       14     65536         8

       15     65536         8

       16   1048576       128

17 rows selected.

trucate will shrink the table and indexes to the original size when table was created without records and it will also reset the high water mark.

SQL> truncate table test;

Table truncated.

SQL> select extent_id, bytes, blocks

    from user_extents

    where segment_name = 'ARUN_T';

EXTENT_ID     BYTES    BLOCKS

        0     65536         8

              **** END of Listing 5 ****

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

Listing 6 Drop Storage and Reuse Storage with truncate and effects

SQL> begin

  2  for i in 1..1000000 loop

  3  insert into test_hwm values (I);

  4  end loop;

  5  commit;

  6  end;

  7  /

PL/SQL procedure successfully completed.

SQL> select blocks from dba_segments where owner='SCOTT' and segment_name ='TEST_HWM';

BLOCKS

-------

 1664

SQL> select count(*) from test_hwm ;

COUNT(*)

--------

1000000

SQL> truncate table test_hwm reuse storage

  2  /

Table truncated.

SQL> select blocks from dba_segments where owner='SCOTT' and segment_name ='TEST_HWM';

BLOCKS

-------

 1664

SQL> select count(*) from test_hwm ;

COUNT(*)

--------

0

SQL> truncate table test_hwm drop storage ;

Table truncated.

SQL> select blocks from dba_segments where owner='SCOTT' and segment_name ='TEST_HWM';

BLOCKS

-------

 8

SQL> select count(*) from test_hwm;

  COUNT(*)

--------

0

              **** END of Listing 6 ****

      Listing 7 High Water Mark Test

Brief about High water Mark for more details search www.google.com

High Water Mark (HWM)

It is an Indicator which tells how much data a block ever have or

Oracle uses the high water mark to identify the highest amount of space used by a particular segment. It acts as the boundary between used and unused space. As the amount of data grows due to row inserts and updates, the segment's high water mark grows accordingly. 

The database doesn't know what is on a block unless and until, It goes to the block.

So, when you delete the information, the block is still "a block", it is just a block that once had active rows - but no longer does.
And when you full scan the table - we have to read all blocks that at any time contained data - because - they could contain data now. We won't know what is there until we read it.

Set autotrace on

SQL> create table test_hwm (id number);

Table created.

SQL> select blocks from dba_segments where owner='SCOTT' and segment_name ='TEST_HWM';

BLOCKS

-------

 8

SQL> begin

  2  for i in 1..1000000 loop

  3  insert into test_hwm values(i);

  4  end loop;

  5  commit;

  6  end;

  7  /

PL/SQL procedure successfully completed.

SQL> select blocks from dba_segments where owner='SCOTT' and segment_name ='TEST_HWM';

BLOCKS

-------

 8

SQL> select count(*) from test_hwm ;

       COUNT(*)

----------------

       1000000

Execution Plan

----------------------------------------------------------

Plan hash value: 2326001506

----------------------------------------------------------

| Id  | Operation          | Name     | Rows  | Cost (%CPU)| Time     |

-----------------------------------------------------------------------

|   0 | SELECT STATEMENT   |          |     1 |   369   (7)| 00:00:05 |

|   1 |  SORT AGGREGATE    |          |     1 |            |          |

|   2 |   TABLE ACCESS FULL| TEST_HWM |   902K|   369   (7)| 00:00:05 |

-----------------------------------------------------------------------  

-- See time to count the records is 05 seconds.

SQL> delete from test_hwm where id<10000;

  9999 rows deleted.

Execution Plan

----------------------------------------------------------

Plan hash value: 2971358912

-------------------------------------------------------------------------------

| Id  | Operation          | Name     | Rows  | Bytes | Cost (%CPU)| Time     |

-------------------------------------------------------------------------------

 |   0 | DELETE STATEMENT   |          |    17 |   221 |   376   (9)| 00:00:05 |

 |   1 |  DELETE            | TEST_HWM |       |       |            |          |

 |*  2 |   TABLE ACCESS FULL| TEST_HWM |    17 |   221 |   376   (9)| 00:00:05 | 

-------------------------------------------------------------------------------

-- Note Blocks consumed or Still same.

SQL> select blocks from dba_segments where owner='SCOTT' and segment_name ='TEST_HWM';

       BLOCKS

       ---------

       1664

SQL> rollback;

 

Rollback complete.

-- delete all the records

SQL> delete from test_hwm ;

1000000 rows deleted.

-- Time taken to count the o records are same 0.5 seconds

SQL> select count(*) from test_hwm ;

       COUNT(*)

    -------------

         0          

Execution Plan

----------------------------------------------------------

Plan hash value: 2326001506

----------------------------------------------------------

| Id  | Operation          | Name     | Rows  | Cost (%CPU)| Time     |

-----------------------------------------------------------------------

|   0 | SELECT STATEMENT   |          |     1 |   369   (7)| 00:00:05 |

|   1 |  SORT AGGREGATE    |          |     1 |            |          |

|   2 |   TABLE ACCESS FULL| TEST_HWM |   902K|   369   (7)| 00:00:05 |

-----------------------------------------------------------------------  

 

  -- Note blocks are still same

 SQL> select blocks from dba_segments where owner='SCOTT' and segment_name ='TEST_HWM';

       BLOCKS

       ---------

       1664

SQL> rollback;

Rollback complete.

SQL> select count(*) from test_hwm ;

COUNT(*)

    -------------

         1000000    

Plan hash value: 2326001506

----------------------------------------------------------

| Id  | Operation          | Name     | Rows  | Cost (%CPU)| Time     |

-----------------------------------------------------------------------

|   0 | SELECT STATEMENT   |          |     1 |   369   (7)| 00:00:05 |

|   1 |  SORT AGGREGATE    |          |     1 |            |          |

|   2 |   TABLE ACCESS FULL| TEST_HWM |   902K|   369   (7)| 00:00:05 |

-----------------------------------------------------------------------  

-- truncate table which reset the HWM and time to find the o record reduce to 0.1 sec.

SQL> truncate table test_hwm

  2  /

Table truncated.

SQL> select blocks from dba_segments where owner='SCOTT' and segment_name ='TEST_HWM';

       BLOCKS

       ---------

         8

SQL> select count(*) from test_hwm

COUNT(*)

    -------------

         0

Execution Plan

----------------------------------------------------------

 Plan hash value: 2326001506

----------------------------------------------------------   

| Id  | Operation          | Name     | Rows  | Cost (%CPU)| Time     |

-----------------------------------------------------------------------

|   0 | SELECT STATEMENT   |          |     1 |     2   (0)| 00:00:01 |

|   1 |  SORT AGGREGATE    |          |     1 |            |          |

|   2 |   TABLE ACCESS FULL| TEST_HWM |     1 |     2   (0)| 00:00:01 |

-----------------------------------------------------------------------

SQL> spool off

                  **** END of Listing 7 ****

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

List 8 .Cluster With Truncate & delete 

--create cluster

SQL> create cluster cluster_test (id number(2));

Cluster created.

--=create cluster tables

SQL> create table cluster_table1 (roll number, name1 varchar2(100),id number(2)) cluster cluster_test(id);

Table created.

SQL> create table cluster_table2 (roll1 number, name2 varchar2(100),id number(2)) cluster cluster_test(id);

Table created.

SQL> insert into cluster_table1 values(1,'arun',1);

insert into cluster_table1 values(1,'arun',1)

            *

ERROR at line 1:

ORA-02032: clustered tables cannot be used before the cluster index is built

--cluster index

SQL> create index c_i on cluster cluster_test;

Index created.

SQL> insert into cluster_table1 values(1,'arun',1);

1 row created.

--Delete from cluster table will work

SQL> delete from cluster_table1;

2 rows deleted.

SQL> rollback;

Rollback complete.

--we  can not truncate a table which is part of cluster

SQL> truncate table cluster_table1;

  truncate table cluster_table1

               *

ERROR at line 1:

ORA-03292: Table to be truncated is part of a cluster

SQL> truncate cluster cluster_test;

  Cluster truncated.

SQL> select count(*) from cluster_table1;

COUNT(*)

--------

0

   SQL> truncate table cluster_table1;

truncate table cluster_table1                *

ERROR at line 1:

ORA-03292: Table to be truncated is part of a cluster

         **** END of Listing 8 ****

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

           

Listing 9 RETURNING Clause: Example 

       Variable bnd1 number

       Begin 

         DELETE FROM employees

         WHERE employee_id=&empid          

         RETURNING salary INTO: bnd1

         End;

         Print bnd1

         BND1

        2000

        **** END of Listing 9 ****

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

I always welcome your queries and feedback :) 

More details on Our Website