ddl对local partition index的影响,oracle会自动维护ddl对local partition index的影响：
SQL> select name,del_lf_rows,del_lf_rows_len from index_stats;
NAME DEL_LF_ROWS DEL_LF_ROWS_LEN
------------------------------ ----------- ---------------
IDX_T 0 0
SQL> alter table t truncate partition p4;
表被截断。
SQL> analyze index idx_t validate structure;
索引已分析
SQL> select name,del_lf_rows,del_lf_rows_len from index_stats;
NAME DEL_LF_ROWS DEL_LF_ROWS_LEN
------------------------------ ----------- ---------------
IDX_T 0 0
--看来通过analyze index idx_t validate structure;没法验证ddl对local partition index的维护，因为
在truncate partition前后index_stats里的数据没有变化
SQL> select index_name,partition_name from dba_ind_partitions where index_name i
n ('IDX_T','IDX_T1','IDX_T_NAME');
INDEX_NAME PARTITION_NAME
------------------------------ ------------------------------
IDX_T P5
IDX_T P4
IDX_T P3
IDX_T P2
IDX_T P1
SQL> alter table t drop partition p4;
表已更改。
SQL> select index_name,partition_name from dba_ind_partitions where index_name i
n ('IDX_T','IDX_T1','IDX_T_NAME');
INDEX_NAME PARTITION_NAME
------------------------------ ------------------------------
IDX_T P5
IDX_T P3
IDX_T P2
IDX_T P1
--drop partition之后和该分区相关的local partition index也被drop了...
SQL> select object_name,subobject_name,last_ddl_time from dba_objects where obje
ct_name='IDX_T';
OBJECT_NAM SUBOBJECT_NAME LAST_DDL_TIME
---------- ------------------------------ -------------------
IDX_T P1 2010/11/02 12:55:38
IDX_T P2 2010/11/02 12:55:38
IDX_T P3 2010/11/02 12:55:38
IDX_T P5 2010/11/02 12:55:38
IDX_T 2010/11/02 13:42:46
SQL> alter table t truncate partition p3;
表被截断。
SQL> select object_name,subobject_name,last_ddl_time from dba_objects where obje
ct_name='IDX_T';
OBJECT_NAM SUBOBJECT_NAME LAST_DDL_TIME
---------- ------------------------------ -------------------
IDX_T P1 2010/11/02 12:55:38
IDX_T P2 2010/11/02 12:55:38
IDX_T P3 2010/11/02 13:50:29
IDX_T P5 2010/11/02 12:55:38
IDX_T 2010/11/02 13:50:29
--通过ddl里的时间我们可以清楚的发现在truncate partition时oracle维护了
其对应的local index，因为p3对应的ddl时间由原来的12:55:38变成了13:50:29
SQL> select segment_name,partition_name,bytes/1024/1024 m from dba_segments whe
re segment_name='IDX_T';
SEGMENT_NAME PARTITION_NAME M
-------------------- ------------------------------ ----------
IDX_T P1 5
IDX_T P2 5
IDX_T P3 .0625
IDX_T P5 6
SQL> alter table t truncate partition p2;
表被截断。
SQL> select segment_name,partition_name,bytes/1024/1024 m from dba_segments whe
re segment_name='IDX_T';
SEGMENT_NAME PARTITION_NAME M
-------------------- ------------------------------ ----------
IDX_T P1 5
IDX_T P2 .0625
IDX_T P3 .0625
IDX_T P5 6
SQL>
--当然通过local index的大小我们也可以清楚的观查到，local partition index
p2的大小在truncate p2之前是5m，之后变成了0.625m，可见oracle维护了local index
--======================
dml对global partition index的影响：
SQL> select name,del_lf_rows,del_lf_rows_len from index_stats;
NAME DEL_LF_ROWS DEL_LF_ROWS_LEN
------------------------------ ----------- ---------------
IDX_T 0 0
SQL> delete from t where object_id=100;
已删除128行。
SQL> commit;
提交完成。
SQL> analyze index idx_t_g validate structure;
索引已分析
SQL> select name,del_lf_rows,del_lf_rows_len from index_stats;
NAME DEL_LF_ROWS DEL_LF_ROWS_LEN
------------------------------ ----------- ---------------
IDX_T_G 127 2565
SQL>
--很显然oracle会自动维护dml对global partition index的维护，这个其实
不用验证也没有问题，如果不能自动维护dml，那么怎么使用这种类型的index
--=========================
dll对global partition index的影响：
SQL> select object_name,subobject_name,last_ddl_time from dba_objects where obje
ct_name='IDX_T_G';
OBJECT_NAM SUBOBJECT_NAME LAST_DDL_TIME
---------- ------------------------------ -------------------
IDX_T_G GP1 2010/11/02 14:17:49
IDX_T_G GP2 2010/11/02 14:17:49
IDX_T_G GP3 2010/11/02 14:17:49
IDX_T_G GP4 2010/11/02 14:17:49
IDX_T_G GP5 2010/11/02 14:17:49
IDX_T_G GP6 2010/11/02 14:17:49
IDX_T_G GP_MAX 2010/11/02 14:17:49
IDX_T_G 2010/11/02 14:17:49
已选择8行。
SQL> select segment_name,partition_name,bytes/1024/1024 m from dba_segments whe
re segment_name='IDX_T_G';
SEGMENT_NAME PARTITION_NAME M
-------------------- ------------------------------ ----------
IDX_T_G GP1 .0625
IDX_T_G GP2 .0625
IDX_T_G GP3 .0625
IDX_T_G GP4 .0625
IDX_T_G GP5 .0625
IDX_T_G GP6 .0625
IDX_T_G GP_MAX 12
已选择7行。
SQL> alter table t truncate partition p5;
表被截断。
SQL> select object_name,subobject_name,last_ddl_time from dba_objects where obje
ct_name='IDX_T_G';
OBJECT_NAM SUBOBJECT_NAME LAST_DDL_TIME
---------- ------------------------------ -------------------
IDX_T_G GP1 2010/11/02 14:17:49
IDX_T_G GP2 2010/11/02 14:17:49
IDX_T_G GP3 2010/11/02 14:17:49
IDX_T_G GP4 2010/11/02 14:17:49
IDX_T_G GP5 2010/11/02 14:17:49
IDX_T_G GP6 2010/11/02 14:17:49
IDX_T_G GP_MAX 2010/11/02 14:17:49
IDX_T_G 2010/11/02 14:17:49
已选择8行。
SQL> select segment_name,partition_name,bytes/1024/1024 m from dba_segments whe
re segment_name='IDX_T_G';
SEGMENT_NAME PARTITION_NAME M
-------------------- ------------------------------ ----------
IDX_T_G GP1 .0625
IDX_T_G GP2 .0625
IDX_T_G GP3 .0625
IDX_T_G GP4 .0625
IDX_T_G GP5 .0625
IDX_T_G GP6 .0625
IDX_T_G GP_MAX 12
已选择7行。
SQL>
--global index partition GP_MAX记录的数据>9000,而表分区p5的数据>8000,
在truncate p5之后对比GP_MAX前后的ddl修改时间和段大小，发现都没有任何变化,
我就认为ddl对global index partition不自动维护吧，实在没有太好的验证办法，当然
可以dump index的结构来看，感兴趣的自己测试一下吧。
其实从下面查询index的状态就可以看出来，此时的index状态全部变成了UNUSABLE：
SQL> select index_name,partition_name,status from dba_ind_partitions where index
_name in ('IDX_T_G');
INDEX_NAME PARTITION_NAME STATUS
------------------------------ ------------------------------ --------
IDX_T_G GP1 UNUSABLE
IDX_T_G GP2 UNUSABLE
IDX_T_G GP3 UNUSABLE
IDX_T_G GP4 UNUSABLE
IDX_T_G GP5 UNUSABLE
IDX_T_G GP6 UNUSABLE
IDX_T_G GP_MAX UNUSABLE
已选择7行。
SQL>
尝试重建这个global partition index ：
SQL> alter index idx_t_g rebuild;
alter index idx_t_g rebuild
*
第 1 行出现错误:
ORA-14086: 不能将分区索引作为整体重建
SQL>
--看来只能一个一个重建了：
SQL> alter index idx_t_g rebuild partition gp1 ;
索引已更改。
SQL> alter index idx_t_g rebuild partition gp2 ;
索引已更改。
SQL> alter index idx_t_g rebuild partition gp3 ;
索引已更改。
SQL> alter index idx_t_g rebuild partition gp4 ;
索引已更改。
SQL> alter index idx_t_g rebuild partition gp5 ;
索引已更改。
SQL> alter index idx_t_g rebuild partition gp6 ;
索引已更改。
SQL> alter index idx_t_g rebuild partition gp_max ;
索引已更改。
SQL> select index_name,partition_name,status from dba_ind_partitions where index
_name in ('IDX_T_G');
INDEX_NAME PARTITION_NAME STATUS
------------------------------ ------------------------------ --------
IDX_T_G GP1 USABLE
IDX_T_G GP2 USABLE
IDX_T_G GP3 USABLE
IDX_T_G GP4 USABLE
IDX_T_G GP5 USABLE
IDX_T_G GP6 USABLE
IDX_T_G GP_MAX USABLE
已选择7行。
SQL>
--重建之后状态变成了USABLE，注意只要我们truncate一个分区，那么global
partition index的分区index的状态全部变成UNUSABLE
如何自动维护global partition index：
SQL> select index_name,partition_name,status from dba_ind_partitions where index
_name in ('IDX_T_G');
INDEX_NAME PARTITION_NAME STATUS
------------------------------ ------------------------------ --------
IDX_T_G GP1 USABLE
IDX_T_G GP2 USABLE
IDX_T_G GP3 USABLE
IDX_T_G GP4 USABLE
IDX_T_G GP5 USABLE
IDX_T_G GP6 USABLE
IDX_T_G GP_MAX USABLE
已选择7行。
SQL> alter table t truncate partition p1 update global indexes;
表被截断。
SQL> select index_name,partition_name,status from dba_ind_partitions where index
_name in ('IDX_T_G');
INDEX_NAME PARTITION_NAME STATUS
------------------------------ ------------------------------ --------
IDX_T_G GP1 USABLE
IDX_T_G GP2 USABLE
IDX_T_G GP3 USABLE
IDX_T_G GP4 USABLE
IDX_T_G GP5 USABLE
IDX_T_G GP6 USABLE
IDX_T_G GP_MAX USABLE
已选择7行。
SQL>
显然在ddl语句truncate后面加上update global indexes全局分区index的状态都变成了USABLE
，很显然oracle自动为了index，不过如果分区表和分区index比较大的话，那么update global indexes
的处理时间可能会很长，通过锁定表的时间也会很长，所以使用update global indexes子句要慎重。
如果全局分区index的状态原来就是UNUSABLE，那么即使加上update global indexes子句，oracle也不会
自动维护index：
SQL> select index_name,partition_name,status from dba_ind_partitions where index
_name in ('IDX_T_G');
INDEX_NAME PARTITION_NAME STATUS
------------------------------ ------------------------------ --------
IDX_T_G GP1 UNUSABLE
IDX_T_G GP2 UNUSABLE
IDX_T_G GP3 UNUSABLE
IDX_T_G
oracle视频教程请关注:http://u.youku.com/user_video/id_UMzAzMjkxMjE2.html
