Oracle技术和分区表相关的一点总结（五）-白红宇

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

阅读量：5942 次

发布时间：2019-06-19

本文共 8016 字，大约阅读时间需要 26 分钟。

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视频教程请关注:

转载于:https://blog.51cto.com/19880614/1246193

你可能感兴趣的文章

学习，从这一刻开始

查看>>

55：Mysql用户管理|常用sql语句|mysql数据库备份恢复

查看>>

java WebSocket实现一对一消息和广播消息Demo

Oracle Study之--PL/SQL Developer软件错误

查看>>

消息中间件-Activemq之Broker-Cluster

查看>>

JavaScript箭头函数（Arrow Function）

查看>>

ThinkPHP微信实例——JSSDK图像接口多张图片上传下载并将图片流写入本地

查看>>

android ant打包

查看>>

Creating my own systemd service files on Fedora 16(x86_64)

查看>>

VI连接esx主机时报错“client.xml”错误

用Netscaler的Variable和Assignment来实现计数控制

查看>>