深入解析delete和truncate不同之處:

深入解析delete和truncate不同之處:

1.delete與trucate都用來刪除表中的資料，那它們有什麼不同之處呢？我先概要的將它們的不同之處說出，然後用測試依次來證實我所說的不同之處。
2.delete是DML操作，需要生undo成資料，在沒有commit前可以回滾。truncate是DDL操作，不需要生成undo資料，也無法回滾。
3.delte操作並不降低水位線hwm，也就是無法釋放被刪除記錄所佔用的塊空間，undo相反，降低hwm，釋放被刪除記錄所佔用的表空間。
4.在對整個表和索引進行完全瀏覽時，經過TRUNCATE操作後的表比DELETE操作後的表要快得多。
5.detele的執行速度要遠遠低於truncate，就像要求你把一本書扔掉，detele操作是一頁一頁撕下來，
 而truncate則是把整本書直接扔到垃圾桶，造成它們速度差距的根本原因除了有無undo資料的生成之外，
 和它們在塊上的實現操作有關.
6.TRUNCATE不能觸發任何DELETE觸發器
7.不能授予任何人清空他人的表的許可權
8.不能清空父表

1.=====> 對空間的影響
++++++++++++++++++++++++++++++++++++++++++++
 SQL> create table Tmall as select * from dba_objects;

Table created.

SQL> insert into Tmall select * from dba_objects;

75091 rows created.

SQL> /

75091 rows created.

SQL> /

75091 rows created.

SQL> /

75091 rows created.

                                                                                                                                          43
SQL> col SEGMENT_NAME for a10 
SQL> select segment_name, bytes/1024/1024  from dba_segments where  segment_name='TMALL';

SEGMENT_NA BYTES/1024/1024
---------- ---------------
TMALL                   43

SQL> select  count(*)  from dba_extents where segment_name='TMALL';

  COUNT(*)
----------
        58

============
SQL> delete from TMALL;

375455 rows deleted.

SQL> commit;

Commit complete.

SQL> select segment_name, bytes/1024/1024  from dba_segments where  segment_name='TMALL';

SEGMENT_NA BYTES/1024/1024
---------- ---------------
TMALL                   43

SQL> select  count(*)  from dba_extents where segment_name='TMALL';

  COUNT(*)
----------
        58
  
---&gt delete 掉後,表Tmall所佔用的extent為58個.

SQL> truncate table TMALL;

Table truncated.

SQL> select segment_name, bytes/1024/1024  from dba_segments where  segment_name='TMALL';

SEGMENT_NA BYTES/1024/1024
---------- ---------------
TMALL                .0625

SQL> select  count(*)  from dba_extents where segment_name='TMALL';

  COUNT(*)
----------
         1
      
truncate:

 Truncate後塊只有1.

由實驗可見：delete操作不會釋放空間，而Truncate操作會釋放空間。Delete操作後，刪除的是那個表資料騰出空間還只能被那個表使用並不讓其它表使用.

====> 如何釋放delete後仍佔據的空間
SQL> delete from TMALL;

375455 rows deleted.

SQL> select count(*) from tmall;

  COUNT(*)
----------
         0

SQL> select  count(*)  from dba_extents where segment_name='TMALL';

  COUNT(*)
----------
        58

SQL> alter table Tmall move;

Table altered.

SQL> select  count(*)  from dba_extents where segment_name='TMALL';

  COUNT(*)
----------
         1

====> 比較兩者所產生undo的測試
 
SQL>  create table Tmall as select * from dba_objects;

Table created.

SQL> select used_ublk,used_urec from v$transaction;

no rows selected

SQL> delete from Tmall;

75091 rows deleted.

SQL> select used_ublk,used_urec from v$transaction;

 USED_UBLK  USED_UREC
---------- ----------
      2197      75091
+++++++++++++++++++++++++++++++++++++++++++++++++++
delete 產生了大量的undo

SQL> create table Tmall as select * from dba_objects;

Table created.

SQL> select used_ublk,used_urec from v$transaction;

no rows selected

SQL> truncate table TMALL;

Table truncated.

SQL> select used_ublk,used_urec from v$transaction;

no rows selected

++++++++++++++++++
truncate 不產生undo.     

====> 比較兩者所完成的時間
SQL> set timing on
SQL> delete from Tmall;

375455 rows deleted.

Elapsed: 00:00:13.72

SQL> truncate table Tmall;

Table truncated.

Elapsed: 00:00:00.25