ITPub博客

首页 > Linux操作系统 > Linux操作系统 > Truncate Table的drop storage 或reuse storage(转)

Truncate Table的drop storage 或reuse storage(转)

原创 Linux操作系统 作者:nbnbhua 时间:2011-08-15 23:57:26 0 删除 编辑

A, B 为两个Table
A, B 的数据分别放在 erp_data 表空间下
A, B 的索引分别放在 erp_indx 表空间下
那么我们使用下面的两个语句删除两个表中的数据
Truncate table A drop storage ;
Truncate table B reuse storage ;
得到的结果将是:
Truncate table A drop storage ;
--data : 数据部分所在的extent 空间会被释放(释放回收到 minextents个extent),腾出来的空间可以供其它segment 使用 。
--index : B表的index部分会数据删除,extent部分也被释放,剩下第一个extent
--hwm : 会将HWM重新设置到第一个Block的位置(hwm会改变).

Truncate table B reuse storage ;
--data : 数据部分所在的extent 空间不会被回收(仅仅数据会被删除),数据删除之后的freespace 空间只能供本表使用,不可以供其它 segment 使用 。
--index : B表的index部分会数据删除,但是保留extent 部分
--hwm : 会将HWM重新设置到第一个Block的位置(hwm会改变).
1、测试开始,建两张表A,B及相应的索引inx_A,idx_B
 SQL> show user
 USER is "QIUYB"
 SQL> create table A (col number);
 Table created.
 SQL> insert into A values(1);
 1 row created.
 SQL> insert into A select * from A;
 1 row created.
 SQL> /
 2 rows created.
 SQL> /
 4 rows created.
  SQL> /
  8 rows created.
  SQL> /
  16 rows created.
  SQL> /
  32 rows created.
  SQL> /
  64 rows created.
  SQL> /
  128 rows created.
  SQL> /
  256 rows created.
  SQL> /
  512 rows created.
  SQL> /
  1024 rows created.
  SQL> /
  2048 rows created.
  SQL> /
  4096 rows created.
  SQL> /
  8192 rows created.
  SQL> /
  16384 rows created.
  SQL> /
  32768 rows created.
  SQL> /
  65536 rows created.
  SQL> commit;
  Commit complete.
  SQL> create table B as select * from A;
  Table created.
  SQL> create index idx_A on A(col);
  Index created.
  SQL> create index idx_B on B(col);
  Index created.
 
  2、查看一下这四个段对于extent的使用。
  SQL> select segment_name,extent_id,bytes
  2 from user_extents
  3 where segment_name like '%A%' OR segment_name like '%B%'
  4 order by 1,
  5 /
  SEGMENT_NA   EXTENT_ID   BYTES
  ----------            ----------         ----------
  A       0                 65536
  A       1                 65536
  A       2                 65536
  A       3                 65536
  A       4                 65536
  A       5                 65536
  A       6                 65536
  A       7                 65536
  A       8                 65536
  A       9                 65536
  A       10                 65536
  A       11                 65536
  A       12                 65536
  A       13                 65536
  A       14                 65536
  A       15                 65536
  A       16                 1048576
  B       0                 65536
  B       1                 65536
  B       2                 65536
  B       3                 65536
  B       4                 65536
  B       5                 65536
  B       6                 65536
  B       7                 65536
  B       8                 6553
  B       9                 65536
  B       10                 65536
  B       11                 65536
  B       12                 65536
  B       13                 65536
  B       14                 65536
  B       15                 65536
  B       16                 1048576
  IDX_A                   0                  65536
  IDX_A                   1                  65536
  IDX_A                   2                  65536
  IDX_A                   3                  65536
  IDX_A                   4                  65536
  IDX_A                   5                  65536
  IDX_A                   6                  65536
  IDX_A                   7                  65536
  IDX_A                   8                  65536
  IDX_A                   9                  65536
  IDX_A                   10                  65536
  IDX_A                   11                  65536
  IDX_A                   12                  65536
  IDX_A                   13                  65536
  IDX_A                   14                  65536
  IDX_A                   15                  65536
  IDX_A                   16                  1048576
  IDX_A                   17                  1048576
  IDX_B                   0                  65536
  IDX_B                   1                  65536
  IDX_B                   2                  65536
  IDX_B                   3                  65536
  IDX_B                   4                  65536
  IDX_B                   5                  65536
  IDX_B                   6                  65536
  IDX_B                   7        65536
  IDX_B                   8           65536
  IDX_B                   9       65536
  IDX_B                   10      65536
  IDX_B                   11      65536
   IDX_B                   12      65536
   IDX_B                   13      65536
   IDX_B                   14      65536
   IDX_B                   15      65536
   IDX_B                   16     1048576
   IDX_B                   17     1048576
  
   70 rows selected.
  
3、查看A,B表的HWM,计算公式HWM=total_blocks-empty_blocks,可以看到A,B的HWM分别为232,168
SQL> analyze table A compute statistics;
Table analyzed.
SQL> analyze table B compute statistics;
Table analyzed.
SQL> col table_name format a10
SQL> SELECT TABLE_NAME,blocks,empty_blocks,blocks-empty_blocks hwm
2 FROM user_tables
3 WHERE table_name IN ('A','B')
4 /
SQL> set pagesize 10
SQL> /
TABLE_NAME     BLOCKS    EMPTY_BLOCKS  HWM
----------     ----------       ------------          ----------
A        244       12       232
B        212       44       168

4、以DROP STORAGE,REUSE STORAGE两种选项对A,B做不同的操作看差别
SQL> truncate table A DROP STORAGE;
Table truncated.
SQL> truncate table B REUSE STORAGE;
Table truncated.
SQL> SET PAGESIZE 300
SQL> select segment_name,extent_id,bytes
 from user_extents
 where segment_name like '%A%' OR segment_name like '%B%'
 order by 1,2
 /
SEGMENT_NA   EXTENT_ID   BYTES
----------     ----------        ----------
A                 0            65536
B                 0            65536
B                 1            65536
B                 2            65536
B                 3            65536
B                 4            65536
B                 5            65536
B                 6            65536
B                 7            65536
B                 8            65536
B                 9            65536
B                 10           65536
B                 11           65536
B                 12           65536
B                 13           65536
B                 14           65536
B                 15           65536
B                 16           1048576
IDX_A            0            65536
IDX_B            0            65536
IDX_B            1            65536
IDX_B            2            65536
IDX_B            3            65536
IDX_B            4            65536
IDX_B            5            65536
IDX_B            6            65536
IDX_B            7            65536
IDX_B            8           65536
IDX_B            9            65536
IDX_B            10           65536
IDX_B            11           65536
IDX_B            12           65536
IDX_B            13           65536
IDX_B            14           65536
IDX_B            15           65536
IDX_B            16           1048576
IDX_B            17           1048576

37 rows selected.

可以看到A表本身及其索引idx_A的extent都是释放的,都保留到第一个extent。
SQL> analyze table A compute statistics;
Table analyzed.
SQL> analyze table B compute statistics;
Table analyzed.
SQL> SELECT TABLE_NAME,blocks,empty_blocks,blocks-empty_blocks hwm
 FROM user_tables
 WHERE table_name IN ('A','B') ;
 /
TABLE_NAME   BLOCKS   EMPTY_BLOCKS   HWM
 ----------            ----------     ------------              ----------
 A                         0                  8                               -8
 B                         0                 256                            -256
 可以看到两个表的hwm都回到了第一个block。 

摘自:http://space.itpub.net/751371/viewspace-607661

来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/21726588/viewspace-704940/,如需转载,请注明出处,否则将追究法律责任。

下一篇: oracle collection
请登录后发表评论 登录
全部评论

注册时间:2011-07-21

  • 博文量
    7
  • 访问量
    21436