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

TRUNCATEDELETE
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

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 )

Google photo

You are commenting using your Google 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 )

Connecting to %s