DIFFERENCE BETWEEN TRUNCATE AND DELETE ORACLE
Truncate:
- DDL statement
- Removes all rows
- No where clause used
- Cannot be rollback and doesnot generate undo
- Recover space after removing rows and reset high water mark
- Truncate changes the unusable index
- Require exclusive table lock
- Faster
- Efficient to remove tables which are big
Delete:
- DML statement
- Removes one or more rows
- Where clause can be used if a condition is set to remove specific rows
- Can be rollback and generate undo
- Delete doesnot touch the index of table
- Remove one row at a time and save them in logfile
- Require shared table lock
- Slower than truncate
- Efficient to remove specific rows out of total rows in a table
Undo redo generation test for truncate and delete
kish@exdbx<>select name, value from v$mystat ms
inner join v$statname sn on sn.statistic# = ms.statistic#
where sn.name like '%undo change%'
or sn.name = 'redo size'; 2 3 4
NAME VALUE
---------------------------------------------------------------- ----------------------------------------
redo size 0
undo change vector size 0
Checking undo and redo generation for truncate
kish@exdbx<>truncate table d;
Table truncated.
Elapsed: 00:00:00.19
kish@exdbx<>select name, value from v$mystat ms
inner join v$statname sn on sn.statistic# = ms.statistic#
where sn.name like '%undo change%'
or sn.name = 'redo size'; 2 3 4
NAME VALUE
---------------------------------------------------------------- ----------------------------------------
redo size 6780
undo change vector size 1836
Checking redo and undo generation for delete
kish@exdbx<>delete e;
7 rows deleted.
Elapsed: 00:00:00.00
kish@exdbx<>commit;
Commit complete.
kish@exdbx<>select name, value from v$mystat ms
inner join v$statname sn on sn.statistic# = ms.statistic#
where sn.name like '%undo change%'
or sn.name = 'redo size'; 2 3 4
NAME VALUE
---------------------------------------------------------------- ----------------------------------------
redo size 8880
undo change vector size 2664
| TRUNCATE | DELETE |
| DDL statement | DML statement |
| Removes all rows | Removes one or more rows |
| No where clause used | Where clause can be used if a condition is set to remove specific rows |
| Cannot be rollback and doesnot generate undo | Can be rollback and generate undo |
| Recover space after removing rows and reset high water mark | Delete doesnot touch the index of table |
| Truncate changes the unusable index | Delete doesnot touch the index of table |
| Require exclusive table lock | Require shared table lock |
| Faster | Slower than truncate |