验证分区表中添加或删除空分区对全局索引没影响
建立分区表及主键和一个全局索引：
SQL> create table t (id number,sale_date date)
2 partition by range(sale_date)(
3 partition p_2012_10 values less than(to_date('2012-11-01','yyyy-mm-dd')));
Table created
SQL> alter table t add constraint pk_t primary key (id);
Table altered
SQL> create index idx_t on t (sale_date);
Index created
插入数据：
SQL> insert into t values (1,sysdate-30);
1 row inserted
SQL> commit;
Commit complete
检查分区表及索引状态情况：
SQL> select segment_name,partition_name,blocks,bytes from user_segments where segment_name='T';
SEGMENT_NAME PARTITION_NAME BLOCKS BYTES
-------------------------- ------------------------------ ---------- ----------
T P_2012_10 8 65536
SQL> select * from t;
ID SALE_DATE
---------- -----------
1 2012-10-25
SQL> select index_name,status from user_indexes where index_name in ('PK_T','IDX_T');
INDEX_NAME STATUS
------------------------------ --------
IDX_T VALID
PK_T VALID
SQL> select object_name,status from user_objects where object_name in ('PK_T','IDX_T');
OBJECT_NAME STATUS
-------------------------------------------------------------------------------- -------
IDX_T VALID
PK_T VALID
添加一个空分区，然后查看索引状态：
SQL> alter table t add partition p_2012_11 values less than(to_date('2012-12-01','yyyy-mm-dd'));
Table altered
SQL> select segment_name,partition_name,blocks,bytes from user_segments where segment_name='T';
SEGMENT_NAME PARTITION_NAME BLOCKS BYTES
-------------------------- ------------------------------ ---------- ----------
T P_2012_10 8 65536
T P_2012_11 8 65536
SQL> select index_name,status from user_indexes where index_name in ('PK_T','IDX_T');
INDEX_NAME STATUS
------------------------------ --------
IDX_T VALID
PK_T VALID
SQL> select object_name,status from user_objects where object_name in ('PK_T','IDX_T');
OBJECT_NAME STATUS
-------------------------------------------------------------------------------- -------
IDX_T VALID
PK_T VALID
删除一个空分区，然后查看索引状态：
SQL> alter table t drop partition p_2012_11;
Table altered
SQL> select segment_name,partition_name,blocks,bytes from user_segments where segment_name='T';
SEGMENT_NAME PARTITION_NAME BLOCKS BYTES
--------------------------- ------------------------------ ---------- ----------
T P_2012_10 8 65536
SQL> select index_name,status from user_indexes where index_name in ('PK_T','IDX_T');
INDEX_NAME STATUS
------------------------------ --------
IDX_T VALID
PK_T VALID
SQL> select object_name,status from user_objects where object_name in ('PK_T','IDX_T');
OBJECT_NAME STATUS
-------------------------------------------------------------------------------- -------
IDX_T VALID
PK_T VALID
模拟一个有数据的分区，删除分区内数据后，再删除这个分区，最后查看索引状态：
SQL> alter table t add partition p_2012_11 values less than(to_date('2012-12-01','yyyy-mm-dd'));
Table altered
SQL> insert into t values (2,sysdate);
1 row inserted
SQL> commit;
Commit complete
SQL> delete t where id=2;
1 row deleted
SQL> commit;
Commit complete
SQL> alter table t drop partition p_2012_11;
Table altered
SQL> select segment_name,partition_name,blocks,bytes from user_segments where segment_name='T';
SEGMENT_NAME PARTITION_NAME BLOCKS BYTES
------------------------------ ------------------------------ ---------- ----------
T P_2012_10 8 65536
SQL> select index_name,status from user_indexes where index_name in ('PK_T','IDX_T');
INDEX_NAME STATUS
------------------------------ --------
IDX_T VALID
PK_T VALID
SQL> select object_name,status from user_objects where object_name in ('PK_T','IDX_T');
OBJECT_NAME STATUS
-------------------------------------------------------------------------------- -------
IDX_T VALID
PK_T VALID
查看sql的执行计划，看全局索引是否仍能用：
SQL> set autot on exp
SQL> select id from t where id=1;
ID
----------
1
Execution Plan
----------------------------------------------------------
Plan hash value: 1517170033
--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 13 | 0 (0)| 00:00:01 |
|* 1 | INDEX UNIQUE SCAN| PK_T | 1 | 13 | 0 (0)| 00:00:01 |
--------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - access("ID"=1)
以上实验充分验证了分区表中添加或删除空分区对全局索引没影响。
来源：http://pandarabbit.blog.163.com/blog/static/209284144201210269519867/