表碎片整理时shrink和move的选择

一、表的占用空间大小释放原理
背景：生产环境中，经常会遇到表由于数据不断插入，导致空间越来越大，由于前期配置问题，没有做分区或者其他优化，而且生产数据实时向表插入。要删除历史数据来释放空间。


分析：
（1）没做分区表：
由于没有分区表，所以无法分块对表进行清理。
（2）表实时插入数据，数据量大，要保留部分数据：
无法采用将有效数据插入另一个表，drop原表，修改名字来删除原表，或者truncate原表，来释放空间。
（3）删除历史数据：
用delete from 加条件，删除历史数据。


实验：
首先查看表段使用的大小
SQL> select round(BYTES/1024/1024,2)||'M' MB from user_segments where segment_name='T1';


MB
--------------------------------------------------------------------------------
19.94M




对t1表批量删除操作
declare
   row_num number :=0;
 begin
   for g_t1 in (select rowid from t1 t where t.OBJECT_ID <=10000 ) loop
     delete from t1 where rowid=g_t1.rowid;
     row_num :=row_num+1;
   if mod(row_num,200) =0 THEN
   commit;
  end if;
 end loop;
 commit;
 end;  


PL/SQL 过程已成功完成。


再次查看段的大小
SQL> select round(BYTES/1024/1024,2)||'M' MB from user_segments where segment_name='T1';


MB
--------------------------------------------------------------------------------
19.94M




还是7.13M没有变化。但是表里已经没有数据了。
这里注意，再次用插入命令新的数据，表的大小没有变化。由此可见，数据的空间确实被释放了，可以存储其他信息，但是从查询上看，表占用的空间大小没有缩小，没有整理表碎片。（高水位问题）。
如果需要将表的空间进行缩小，有两个办法：
（1）move
（2）shrink


1、对于空间的要求，shrink不需要额外的空间，move需要两倍的空间；
2、shrink的算法是从segment的底部开始，移动row到segment的顶部，移动的过程相当于delete/insert操作的组合，在这个过程中会产生大量的undo和redo信息；
3、move是直接移动数据块的位置，鉴于上面的原因，在使用shrink的时候，耗时可能非常长，通常慢于move；
4、move是不能在线进行的，而且move后相应的索引也会失效。shrink的一个优点是能在线进行，不影响表上的DML操作，当然，并发的DML操作在shrink结束的时刻会出现短暂的block；shrink的另外一个优点是在碎片整理结束后，表上相关的index仍然enable。




shrink操作
对于一般系统，可采用shrink方式（仅仅适用于堆表,且位于自动段空间管理的表空间(堆表包括:标准表,分区表,物化视图容器,物化视图日志表)）：
（1）启用行记录转移(enable row movement)
alter table t1 enable row movement ;
（2）这里先执行compact 再 到空闲时间执行 cascade
ALTER TABLE t1 SHRINK SPACE COMPACT
ALTER TABLE t1 SHRINK SPACE cascade?
--compact:仅仅是缩小表和索引，并不移动高水位线，不释放空间
--cascade:缩小表及其索引，并移动高水位线，释放空间




move操作：
对于重要系统，最好还是采用move


（1）move前最好逻辑备份待整理的表；
（2）--查看索引
select index_name,table_name,tablespace_name,index_type,status from dba_indexes where table_owner='TT';
（3）对于大表，建议开启并行和nologging
alter table T1 MOVE nologging parallel 2;
（4）整理完毕后重建相关的索引
alter index IND_BIG_TABLE_ID rebuild nologging parallel 2;
（5）恢复表和索引的并行度、logging
alter table T1 logging parallel 1;


最后再次查看
SQL>select round(BYTES/1024/1024,2)||'M' from user_segments where segment_name='T1';






//表上有哪些索引，状态如何
select status,T.* from user_indexes T where table_name=upper('表名');
//查询某个索引的状态
select status,index_name from user_indexes s where index_name=upper('索引名称');
//查询分区索引
select status,index_name from user_ind_partitions s where index_name=upper('索引名称');




索引分析
analyze index 索引名称 validate structure;
select (del_lf_rows_len/lf_rows_len) from index_stats where name='索引名称';