测试oracle 的delete无法释放表空间

2019独角兽企业重金招聘Python工程师标准>>>

#################测试delete无法释放表空间，主要通过全盘扫描的磁盘I/O的开销推断

随着数据的 insert，所使用段(Segment)的数据块(data block)也不断增加，这时候高水位(HWM)也随着上升。当数据被删除后（无论是 delete 还是 truncate table）虽然被占用的数据块(data block)已经相应减少，但是高水位(HWM)并不会随之下降。当高水位(HWM)下存在大量的空白数据块(data block)时，如果发生全表扫描(Full Table Scan, FTS)就会造成很多额外的 IO。因为全表扫描(FTS)的时候读取段(Segment)中的数据块(data block)会一直读取到高水位(HWM)才结束


SQL> create table t as select * from dba_objects;

Table created.

SQL> set autotrace on;
SQL> select count(*) from t;

  COUNT(*)
----------
     72653


Execution Plan
----------------------------------------------------------
Plan hash value: 2966233522

-------------------------------------------------------------------
| Id  | Operation       | Name | Rows  | Cost (%CPU)| Time      |
-------------------------------------------------------------------
|   0 | SELECT STATEMENT   |      |    1 |   290   (1)| 00:00:04 |
|   1 |  SORT AGGREGATE    |      |    1 |           |      |
|   2 |   TABLE ACCESS FULL| T      | 76948 |   290   (1)| 00:00:04 |
-------------------------------------------------------------------

Note
-----
   - dynamic sampling used for this statement (level=2)


Statistics
----------------------------------------------------------
     28  recursive calls
      0  db block gets
       1113  consistent gets     ---磁盘I/O
       1035  physical reads
      0  redo size
    528  bytes sent via SQL*Net to client
    523  bytes received via SQL*Net from client
      2  SQL*Net roundtrips to/from client
      0  sorts (memory)
      0  sorts (disk)
      1  rows processed

SQL> set autotrace off;

SQL> delete from t;

SQL> select count(*) from t;

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

SQL> commit;
SQL> set autotrace on;
SQL> select count(*) from t;

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


Execution Plan
----------------------------------------------------------
Plan hash value: 2966233522

-------------------------------------------------------------------
| Id  | Operation       | Name | Rows  | Cost (%CPU)| Time      |
-------------------------------------------------------------------
|   0 | SELECT STATEMENT   |      |    1 |   289   (0)| 00:00:04 |
|   1 |  SORT AGGREGATE    |      |    1 |           |      |
|   2 |   TABLE ACCESS FULL| T      |    1 |   289   (0)| 00:00:04 |
-------------------------------------------------------------------

Note
-----
   - dynamic sampling used for this statement (level=2)


Statistics
----------------------------------------------------------
      0  recursive calls
      0  db block gets
       1040  consistent gets   ---磁盘I/O没有下降
      0  physical reads
      0  redo size
    525  bytes sent via SQL*Net to client
    523  bytes received via SQL*Net from client
      2  SQL*Net roundtrips to/from client
      0  sorts (memory)
      0  sorts (disk)
      1  rows processed

SQL> truncate table t;

SQL> select count(*) from t;

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


Execution Plan
----------------------------------------------------------
Plan hash value: 2966233522

-------------------------------------------------------------------
| Id  | Operation       | Name | Rows  | Cost (%CPU)| Time      |
-------------------------------------------------------------------
|   0 | SELECT STATEMENT   |      |    1 |    2   (0)| 00:00:01 |
|   1 |  SORT AGGREGATE    |      |    1 |           |      |
|   2 |   TABLE ACCESS FULL| T      |    1 |    2   (0)| 00:00:01 |
-------------------------------------------------------------------

Note
-----
   - dynamic sampling used for this statement (level=2)


Statistics
----------------------------------------------------------
     20  recursive calls
      1  db block gets
     10  consistent gets          ---显著下降
      0  physical reads
     96  redo size
    525  bytes sent via SQL*Net to client
    523  bytes received via SQL*Net from client
      2  SQL*Net roundtrips to/from client
      0  sorts (memory)
      0  sorts (disk)
      1  rows processed

总结：表明delete删除并不能释放表空间，虽然delete将很多记录删除，以空块保留下来，oracle查询时依然会去查询之前分配的空块。

1.delete产生rollback，如果删除大数据量的表速度会很慢，同时会占用很多的rollback segments .truncate 是DDL操作，不产生rollback，速度快一些.
　　
　　Truncate table does not generate rollback information and redo records so it is much faster than delete.
　　
　　In default, it deallocates all space except the space allocated by MINEXTENTS unless you specify REUSE STORAGE clause.
　　
2.delete不从tablespace中腾出空间,需要
　　
　　ALTER TABLESPACE AAA COALESCE(合并); 才有空间
　　
3.truncate 调整high water mark 而delete不.truncate之后，TABLE的HWM退回到 INITIAL和NEXT的位置（默认）
　　
　　delete 则不可以。
　　
4.truncate 只能对TABLE
　　
　　delete 可以是table,view,synonym
5.、 truncate是DDL語言.
delete是DML語言

DDL語言是自動提交的.
命令完成就不可回滾.

转载于:https://my.oschina.net/u/1458120/blog/225671