最初由 tolywang 发布
[B]总结一下 [ 不妥的部分请帮忙更正 ]
Truncate table A drop storage ;
--index : B表的index部分会数据删除,但是保留extent 部分
[/B]
这一句有一些问题,extent部分也会释放,只剩下第一个extent。
如下为我的测试:
[php]
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,2
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 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_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
2 from user_extents
3 where segment_name like '%A%' OR segment_name like '%B%'
4 order by 1,2
5 /
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
2 FROM user_tables
3 WHERE table_name IN ('A','B')
4 /
TABLE_NAME BLOCKS EMPTY_BLOCKS HWM
---------- ---------- ------------ ----------
A 0 8 -8
B 0 256 -256
可以看到两个表的hwm都回到了第一个block。
sssss
[/php]