关于数据清理时Global and Local index

此小文不讨论分区索引的其它方面如：本地非前缀索引和本地前缀索引，以及分区裁剪和分区索引的主键问题，我们看看清理数据时需要注意的地方。
经常需要清理数据，而这些表中基本都是分区表。那么，我们如何来确定那些表分区可以清理(本地索引，partition可以truncate)，如果有global索引，如何确定：
创建测试表：
create table test_par 
(vid number ,
v_date number,
v_data varchar2(100))
partition by range(v_date)
(partition day_1 values less than (2 ),
partition day_2 values less than (3 ),
partition day_3 values less than (4 ),
partition day_4 values less than (5 ),
partition day_5 values less than (6 ),
partition day_6 values less than (7 ),
partition day_7 values less than (8 ),
partition day_8 values less than (9 ),
partition day_9 values less than (10),
partition day_10 values less than (11),
partition day_11 values less than (12),
partition day_12 values less than (13),
partition day_13 values less than (14),
partition day_14 values less than (15),
partition day_15 values less than (16),
partition day_16 values less than (17),
partition day_17 values less than (18),
partition day_18 values less than (19),
partition day_19 values less than (20),
partition day_20 values less than (21),
partition day_21 values less than (22),
partition day_22 values less than (23),
partition day_23 values less than (24),
partition day_24 values less than (25),
partition day_25 values less than (26),
partition day_26 values less than (27),
partition day_27 values less than (28),
partition day_28 values less than (29),
partition day_29 values less than (30),
partition day_30 values less than (31),
partition day_31 values less than (32), 
partition day_max values less than (maxvalue)
);
插入测试数据：
SQL> insert into test
  2  (vid,
  3  v_date,
  4  v_data)
  5  select 
  6  level ,
  7  mod(level,31)+1,
  8  lpad(level,60,'0')
  9  from dual 
 10  connect by level<=1000000;
1000000 rows created.
SQL> commit;
Commit complete.

创建索引global nonpartition index：
create index in1 on test(vid) tablespace sec;
然后收集统计信息：
begin
   dbms_stats.gather_table_stats(ownname =>'SYS',
                                 tabname =>'TEST',
                  estimate_percent =>5,
                  method_opt =>'FOR ALL COLUMNS SIZE AUTO',
                  degree =>4,
                  cascade =>TRUE);
end;
/

来看看索引的信息：

select index_name,index_type,status from dba_indexes where table_name='TEST';
INDEX_NAME  INDEX_TYPE STATUS
----------  ---------- ------
IN1          NORMAL    VALID

那么我们truncate一个分区：
SQL> alter table test truncate partition day_31;
Table truncated.

select index_name,index_type,status from dba_indexes where table_name='TEST';
INDEX_NAME  INDEX_TYPE STATUS
----------  ---------- --------
IN1          NORMAL    UNUSABLE

那么我们drop掉次索引，再来创建带有global的索引：

create index in1 on test(vid) global tablespace sec；
INDEX_NAME  INDEX_TYPE STATUS
----------  ---------- ------
IN1          NORMAL    VALID

对于在创建索引时未指定global字段或者local字段，默认索引类型就是全局非分区索引。
我曾经犯过错误，清理表分区之前，我只是去确认dba_part_indexes里面没有找到global类型的索引，那么这个就是
只关注了global 分区索引的存在与否而忽略了normal的global索引。太危险的事情，要是白天业务高峰，truncate了
分区，索引失效，后果不看设想。
下面看看global partition index:

SQL> create index in1 on test(vid) global
  2  partition by range(vid)
  3  (
  4  partition part_1 values less than(300000),
  5  partition part_2 values less than(600000),
  6  partition part_max values less than(MAXVALUE)
  7  )
  8  /

Index created.
1.
select index_name,index_type,status from dba_indexes where table_name='TEST';
INDEX_NAME  INDEX_TYPE STATUS
----------  ---------- --------
IN1          NORMAL    N/A
看到没有，global partition index在dba_indexes里面状态N/A.
2.
select index_name,partition_name,
       status
from dba_ind_partitions where index_name='IN1';
INDEX_NAME	PARTITION_NAME	STATUS
----------  -------------   ------
IN1	        PART_1	        USABLE
IN1	        PART_2	        USABLE
IN1	        PART_MAX	      USABLE
3.
select index_name,locality from dba_part_indexes where table_name='TEST';
INDEX_NAME	LOCALITY
----------  --------
IN1	        GLOBAL

那么我要是在truncate掉一个分区呢？
SQL> alter table test truncate partition day_30;

Table truncated.

SQL> alter table test truncate partition day_30;

Table truncated.

1.
select index_name,index_type,status from dba_indexes where table_name='TEST';
INDEX_NAME	INDEX_TYPE	STATUS
---------   ----------  ------
IN1	        NORMAL	    N/A
这时候global partition index在dba_indexes里面的status是没有变化的。
2.
select index_name,partition_name,
       status
from dba_ind_partitions where index_name='IN1';
INDEX_NAME	PARTITION_NAME	 STATUS
---------   --------------   ------
IN1	        PART_1	         UNUSABLE
IN1	        PART_2	         UNUSABLE
IN1	        PART_MAX	       UNUSABLE
3.
select index_name,locality from dba_part_indexes where table_name='TEST';
INDEX_NAME	LOCALITY
---------   --------
IN1	        GLOBAL

当然，我们如果使用update global index，dba_ind_partitions里面的状态是什么呢，看下面神奇的情况.
SQL> alter table test truncate partition day_29 update global indexes;
Table truncated.

select index_name,partition_name,
       status
from dba_ind_partitions where index_name='IN1';
INDEX_NAME	PARTITION_NAME	STATUS
----------  --------------  ------
IN1	        PART_1	        UNUSABLE
IN1	        PART_2	        UNUSABLE
IN1	        PART_MAX	UNUSABLE

索引的状态还是UNUSABLE,我们先抛弃使用update global indexes选项加锁对db的影响，以及会出现library cache等事件的影响。
你还敢在生产上如果存在global partition indexes时候使用update global indexes？
最后来看下我们如何判断索引是local的，这个就比较容易了。
SQL> create index in1 on test(v_date,vid) local tablespace sec;
Index created.

1.
select index_name,index_type,status from dba_indexes where table_name='TEST';
INDEX_NAME	INDEX_TYPE	STATUS
---------   ----------  ------
IN1	        NORMAL	    N/A
2.
select index_name,partition_name,
       status
from dba_ind_partitions where index_name='IN1';
INDEX_NAME	PARTITION_NAME	STATUS
---------   -------------   ------
IN1         DAY_1	         USABLE
IN1         DAY_10	       USABLE
IN1         DAY_11	       USABLE
IN1         DAY_12	       USABLE
IN1         DAY_13	       USABLE
IN1         DAY_14	       USABLE
IN1         DAY_15	       USABLE
IN1         DAY_16	       USABLE
IN1         DAY_17	       USABLE
IN1         DAY_18	       USABLE
IN1         DAY_19	       USABLE
IN1         DAY_2	         USABLE
IN1         DAY_20	       USABLE
IN1         DAY_21	       USABLE
IN1         DAY_22	       USABLE
IN1         DAY_23	       USABLE
IN1         DAY_24	       USABLE
IN1         DAY_25	       USABLE
IN1         DAY_26	       USABLE
IN1         DAY_27	       USABLE
IN1         DAY_28	       USABLE
IN1         DAY_29	       USABLE
IN1         DAY_3	         USABLE
IN1         DAY_30	       USABLE
IN1         DAY_4	         USABLE
IN1         DAY_5	         USABLE
IN1         DAY_6	         USABLE
IN1         DAY_7	         USABLE
IN1         DAY_8	         USABLE
IN1         DAY_9	         USABLE
IN1         DAY_MAX	       USABLE
3.
select index_name,locality from dba_part_indexes where table_name='TEST';
INDEX_NAME	LOCALITY
----------  --------
IN1	        LOCAL

分区索引你就可以truncate一个分区，而不影响其它的了。
再创建一个global非分区索引：
SQL> create index in2 on test(vid) tablespace sec;
Index created.
可以根据如下脚本来查看有无global index：
select owner,
       index_name 
from dba_indexes 
where table_name='&Table_name' and status in('VALID','N/A')
minus
select owner,
       index_name 
from dba_part_indexes 
where table_name='&Table_name' and locality='LOCAL';
如果有返回行，那么你就要去关注了，看看是global partition index还是global nonpartition index。
略记于此。