分区表的分区数据删除、truncate会对全局和局部索引产生什么影响

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

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

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