1.建立測試表
SQL> create table t1 (id int);
Table created.
2.檢視表占用的塊數量
SQL> SELECT blocks, empty_blocks, num_rows FROM dba_tables WHERE table_name='T1';
BLOCKS EMPTY_BLOCKS NUM_ROWS
---------- ------------ ----------
3.對T1表做統計資訊收集
SQL> ANALYZE TABLE T1 COMPUTE STATISTICS;
Table analyzed.
4.再次檢視表占用的塊數量
SQL> SELECT blocks, empty_blocks, num_rows FROM dba_tables WHERE table_name='T1';
BLOCKS EMPTY_BLOCKS NUM_ROWS
---------- ------------ ----------
0 7 0
5.向表中插入1000W條資料
declare
i number;
begin
for i in 1..10000000 loop
insert into t1 values(i);
end loop;
commit;
end;
/
6.檢視表占用的塊數量
SQL> SELECT blocks, empty_blocks, num_rows FROM dba_tables WHERE table_name='T1';
BLOCKS EMPTY_BLOCKS NUM_ROWS
---------- ------------ ----------
0 7 0
7.統計資訊收集
SQL> ANALYZE TABLE T1 COMPUTE STATISTICS;
Table analyzed.
8.再次檢視表占用的塊數量
SQL> SELECT blocks, empty_blocks, num_rows FROM dba_tables WHERE table_name='T1';
BLOCKS EMPTY_BLOCKS NUM_ROWS
---------- ------------ ----------
15155 204 10000000
9.删除資料
SQL> delete from t1;
10000000 rows deleted.
SQL> commit;
Commit complete.
10.檢視表占用的塊數量
SQL> SELECT blocks, empty_blocks, num_rows FROM dba_tables WHERE table_name='T1';
BLOCKS EMPTY_BLOCKS NUM_ROWS
---------- ------------ ----------
15155 204 10000000
11.統計資訊收集
SQL> ANALYZE TABLE T1 COMPUTE STATISTICS;
Table analyzed.
12.再次檢視表占用的塊數量,可以看到
SQL> SELECT blocks, empty_blocks, num_rows FROM dba_tables WHERE table_name='T1';
BLOCKS EMPTY_BLOCKS NUM_ROWS
---------- ------------ ----------
15155 204 0
13.降低高水位操作
SQL> alter table T1 move;
Table altered.
14.再次檢視表占用的塊數量
SQL> SELECT blocks, empty_blocks, num_rows FROM dba_tables WHERE table_name='T1';
BLOCKS EMPTY_BLOCKS NUM_ROWS
---------- ------------ ----------
15155 204 0
15.再次收集統計資訊
SQL> ANALYZE TABLE T1 COMPUTE STATISTICS;
Table analyzed.
16.檢視表占用的塊數量,可以看到執行alter table T1 move指令後,高水位已經下降了。
SQL> SELECT blocks, empty_blocks, num_rows FROM dba_tables WHERE table_name='T1';
BLOCKS EMPTY_BLOCKS NUM_ROWS
---------- ------------ ----------
0 7 0
ANALYZE TABLE T1 COMPUTE STATISTICS; 分析表
SELECT blocks, empty_blocks, num_rows FROM dba_tables WHERE table_name='T1'; 查詢高水位線方法
1:alter table T1 enable row movement; 開啟行遷移(收縮表操做第一步)
alter table T1 shrink space ; 收縮表空間
alter table T1 disable row movement; 關閉行遷移方法
2:alter table T1 move; 移動表需要建立索引
create Index new_index On T1(deptno); 單一索引
create Index new_index on T1(deptno,job); 複合索引方法
3:truncate table T1; 清空表方法
4:create table T1_NEW as select * from T1 where 1=2;
建立新表alter table T1_NEW rename to T1; 重命名新表