和分区表相关的一点总结（五）

 

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

 

 

（以上内容摘于网络，如有侵权，请告之，将第一时间删除）