一、分区表操作

        ① 马上回收空间：

　

alter table table_name truncate partition partition_name drop storage;

　　② 同时维护全局索引：

　

alter table table_name drop partition partition_name update global indexes;

二、对全局索引的作用

　　大分区表truncate partition后，需要对全局索引进行维护，否则，global index会变成unusable

　　问题介绍：

　　① 在drop partition时，为了维护global索引，要加update indexes或是update global indexes条件

　　请问，大家研究过这两个条件的区别吗?

　　答： UPDATE GLOBAL INDEXES只维护全局索引

　　UPDATE INDEXES同时维护全局和本地索引

　　对于DROP/TRUNCATE PARTITION而言 ,二者没有太大的区别

　　对于MERGE和SPLIT PARTITION,你就可以看到二者的区别了

　　虽然index是变得valid了，但是index的空间没有释放

　　因为该操作不等于REBUILD,只是在进行DDL的时候，同步维护索引信息而已

　　工业环境的案例介绍：

　　② 我今天对分区表的一个分区做了TRUNCATE,这个分区有一个普通唯一索引和本地索引，

　　TRUNCATRE的时候没有用UPDATE GLOBAL INDEXES 那个命令，结果导致报：

　　ORA-01502: index BILL.IDX_CHARGE_D_591_0712_SID or partition of such index is in unusable state

　　这是因为，truncate partition不加update global indexes,会导致全局索引失效(unusable)。

　　然后，我只好：

　　alter index bill.IDX_CHARGE_D_591_0712_SID parallel 10 nologging rebuild 来整个的重建，13亿记录的大表

　　后来接着晚上有人继续插入这个表的时候，告诉我慢的要命，本来一个小时至少可以跑完400万条记录，现在3个小时了才跑130万

　　我马上想到会不会是本地索引问题，因为我听说虽然分区交换或者TRUNCATE对局部索引没影响，

　　但是实际上是有问题的，还是重建的好：

　　alter index bill.UNQ_RRATING_CHARGE_D_591_0712 rebuild partition PART_20

　　把这个刚才我TRUNCATE的分区的涉及到的局部索引重新建了一下

　　结果立马见效果了，10分钟跑了200万条记录，600万条记录在20分钟全部跑好!比以前同期跑的还快一点

　　半夜被叫起来干活了

　　奇怪，如下写法怎么半天都执行不好

　

alter table bill.recur_rating_charge_d_591_0712 truncate partition PART_21 update global indexes ;
 　　select count(*) from bill.recur_rating_charge_d_591_0712 partition(PART_21)

　　数据始终不变

　　但是我看v$session_longops看到这个SID很快就做好事了，

　　而我看表分区记录始终在

　　我晕，只好采用老办法，杀掉会话后，

　

alter table bill.RECUR_RATING_CHARGE_d_591_0712 truncate partition PART_20不加update global indexes

　　然后分别维护了普通索引和局部索引，这次加NOLOGGING和PARALLEL 8 ,也很快，3亿的大表，维护普通索引只花了200秒

　

alter index bill.IDX_CHARGE_D_591_0712_SID rebuild parallel 8 nologging ;
 　　alter index bill.UNQ_RRATING_CHARGE_D_591_0712 rebuild partition PART_21 parallel 8 nologging;

　　猜测原因：

　　truncate partition PART_20后，这个分区的和这个分区上的本地索引的统计信息是不会更新也不会丢失

　　当我往这个分区插入数据的时候，执行计划是根据错误的统计信息生成的，所以会很慢

　　当我rebuild index partition PART_20 后，会导致这个索引的统计信息丢失，

　　而我的执行计划就有可能改变了，所以我的插入变快了

　　总结：

　　当你truncate后应该立即对这个分区做分析cascade => true(增加对索引的统计信息)，

　　同时rebuild global index 并分析global index才对

三、空间释放问题

　　其实空间等都已经释放了，但数据字典没有被更新，

　　例如你查dba_segments视图，发现这个分区的bytes其实还为原来的大小

　　我们可执行alter table **** allocate extent即可更新数据字典为正常状态

　　例如针对范围分区如下操作：

　

alter table *** modify partition PART_*** allocate extent;

　　

① 马上回收空间：

　

alter table table_name truncate partition partition_name drop storage;

　　② 同时维护全局索引：

　

alter table table_name drop partition partition_name update global indexes;

　　㈡ 对全局索引的作用

　　大分区表truncate partition后，需要对全局索引进行维护，否则，global index会变成unusable

　　问题介绍：

　　① 在drop partition时，为了维护global索引，要加update indexes或是update global indexes条件

　　请问，大家研究过这两个条件的区别吗?

　　答： UPDATE GLOBAL INDEXES只维护全局索引

　　UPDATE INDEXES同时维护全局和本地索引

　　对于DROP/TRUNCATE PARTITION而言 ,二者没有太大的区别

　　对于MERGE和SPLIT PARTITION,你就可以看到二者的区别了

　　虽然index是变得valid了，但是index的空间没有释放

　　因为该操作不等于REBUILD,只是在进行DDL的时候，同步维护索引信息而已

　　工业环境的案例介绍：

　　② 我今天对分区表的一个分区做了TRUNCATE,这个分区有一个普通唯一索引和本地索引，

　　TRUNCATRE的时候没有用UPDATE GLOBAL INDEXES 那个命令，结果导致报：

　　ORA-01502: index BILL.IDX_CHARGE_D_591_0712_SID or partition of such index is in unusable state

　　这是因为，truncate partition不加update global indexes,会导致全局索引失效(unusable)。

　　然后，我只好：

　　alter index bill.IDX_CHARGE_D_591_0712_SID parallel 10 nologging rebuild 来整个的重建，13亿记录的大表

　　后来接着晚上有人继续插入这个表的时候，告诉我慢的要命，本来一个小时至少可以跑完400万条记录，现在3个小时了才跑130万

　　我马上想到会不会是本地索引问题，因为我听说虽然分区交换或者TRUNCATE对局部索引没影响，

　　但是实际上是有问题的，还是重建的好：

　　alter index bill.UNQ_RRATING_CHARGE_D_591_0712 rebuild partition PART_20

　　把这个刚才我TRUNCATE的分区的涉及到的局部索引重新建了一下

　　结果立马见效果了，10分钟跑了200万条记录，600万条记录在20分钟全部跑好!比以前同期跑的还快一点

　　半夜被叫起来干活了

　　奇怪，如下写法怎么半天都执行不好

　

alter table bill.recur_rating_charge_d_591_0712 truncate partition PART_21 update global indexes ;
 　　select count(*) from bill.recur_rating_charge_d_591_0712 partition(PART_21)

　　数据始终不变

　　但是我看v$session_longops看到这个SID很快就做好事了，

　　而我看表分区记录始终在

　　我晕，只好采用老办法，杀掉会话后，

　

alter table bill.RECUR_RATING_CHARGE_d_591_0712 truncate partition PART_20不加update global indexes

　　然后分别维护了普通索引和局部索引，这次加NOLOGGING和PARALLEL 8 ,也很快，3亿的大表，维护普通索引只花了200秒

　

alter index bill.IDX_CHARGE_D_591_0712_SID rebuild parallel 8 nologging ;
 　　alter index bill.UNQ_RRATING_CHARGE_D_591_0712 rebuild partition PART_21 parallel 8 nologging;

　　猜测原因：

　　truncate partition PART_20后，这个分区的和这个分区上的本地索引的统计信息是不会更新也不会丢失

　　当我往这个分区插入数据的时候，执行计划是根据错误的统计信息生成的，所以会很慢

　　当我rebuild index partition PART_20 后，会导致这个索引的统计信息丢失，

　　而我的执行计划就有可能改变了，所以我的插入变快了

　　总结：

　　当你truncate后应该立即对这个分区做分析cascade => true(增加对索引的统计信息)，

　　同时rebuild global index 并分析global index才对

　　㈢ 空间释放问题

　　其实空间等都已经释放了，但数据字典没有被更新，

　　例如你查dba_segments视图，发现这个分区的bytes其实还为原来的大小

　　我们可执行alter table **** allocate extent即可更新数据字典为正常状态

　　例如针对范围分区如下操作：

　　alter table *** modify partition PART_*** allocate extent;

四、分区表truncate会对全局和局部索引产生什么影响


建立测试表t。

SQL> create table t
2 partition by range (a)
3 (
4 partition p1 values less than (10000) tablespace part1,
5 partition p2 values less than (20000) tablespace part2,
6 partition p3 values less than (30000) tablespace part3,
7 partition p4 values less than (40000) tablespace part4,
8 partition p5 values less than (maxvalue) tablespace part5
9 )
10 as select rownum a ,b.* from all_objects b;

建立一个本地索引

SQL> create index t1 on t(a) local;

Index created

建立一个全局索引

SQL> create index t2 on t(object_id);

Index created

查看索引的状态

SQL> select index_name,status from user_indexes;

INDEX_NAME STATUS
------------------------------ --------
T1 N/A
T2 VALID

SQL> select index_name,partition_name,status from user_ind_partitions
2 ;

INDEX_NAME PARTITION_NAME STATUS
------------------------------ ------------------------------ --------
T1 P1 USABLE
T1 P2 USABLE
T1 P3 USABLE
T1 P4 USABLE
T1 P5 USABLE

删除部分数据

SQL> delete from t where a < 100;

99 rows deleted

SQL> commit;

Commit complete

SQL> select index_name,partition_name,status from user_ind_partitions;

INDEX_NAME PARTITION_NAME STATUS
------------------------------ ------------------------------ --------
T1 P1 USABLE
T1 P2 USABLE
T1 P3 USABLE
T1 P4 USABLE
T1 P5 USABLE

SQL> select index_name,status from user_indexes;

INDEX_NAME STATUS
------------------------------ --------
T1 N/A
T2 VALID

从上面可以看到，删除数据对本地和全局索引都没有什么影响。

看看使用truncate删除表的分区。本地索引没有什么问题，

SQL> alter table t truncate partition p1;

Table truncated

SQL> select index_name,partition_name,status from user_ind_partitions;

INDEX_NAME PARTITION_NAME STATUS
------------------------------ ------------------------------ --------
T1 P1 USABLE
T1 P2 USABLE
T1 P3 USABLE
T1 P4 USABLE
T1 P5 USABLE

全局索引的状态：产生不可用的状态。就是说truncate只是会对全局索引产生影响

SQL> select count(*) from t where a=1000;

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

SQL> select index_name,status from user_indexes;

INDEX_NAME STATUS
------------------------------ --------
T1 N/A
T2 UNUSABLE

总结：

1、删除数据对本地和全局索引都没有什么影响。truncate删除表的分区。本地索引没有问题，但是会对全局索引产生影响