Chinaunix首页 | 论坛 | 博客
  • 博客访问: 2073561
  • 博文数量: 200
  • 博客积分: 2413
  • 博客等级: 大尉
  • 技术积分: 3067
  • 用 户 组: 普通用户
  • 注册时间: 2011-04-01 22:07
  • 认证徽章:
文章分类

全部博文(200)

文章存档

2018年(2)

2017年(8)

2016年(35)

2015年(14)

2014年(20)

2013年(24)

2012年(53)

2011年(44)

分类: Oracle

2012-11-26 22:14:19

使用分区表,要非常注意索引。因为删除、move分区都会造成索引失效。小表的索引和非主键、唯一建索引还好,重建即可。

 

如果是几十G的表,重建索引花费巨大,会严重影响查询性能;

如果是主键或者唯一键索引,索引失效会插入重复的数据,带来的麻烦更大。

 

所以,我们一定要重视分区表操作,避免引起索引失效。

 

哪些操作会引起索引失效?

 ADD PARTITION | SUBPARTITION (hash)

 COALESCE PARTITION | SUBPARTITION (hash)

 DROP PARTITION | SUBPARTITION

 EXCHANGE PARTITION | SUBPARTITION

 MERGE PARTITION | SUBPARTITION

 MOVE PARTITION | SUBPARTITION

 SPLIT PARTITION | SUBPARTITION

 TRUNCATE PARTITION | SUBPARTITION

 

上面所有操作都会引起global索引失效;

 

其中下面的操作会引起local索引失效。

MERGE PARTITION

MOVE PARTITION

SPLIT PARTITION

COALESCE PARTITION

EXCHANGE PARTITION

 

怎么解决索引失效问题,那就是加上update indexes

加上update indexes,以上任何操作不会引起glocal索引失效;

加上update indexes,以上操作中除了EXCHANGE PARTITION操作以外,不会引起local 索引失效。

*EXCHANGE PARTITION操作是个很特殊的操作,加上update indexes参数,EXCHANGE PARTITION依然会造成local 索引失效。

需要注意的是,如果分区中不含数据,上面的操作都不会引起索引失效(EXCHANGE PARTITION除外)。

 

下面是实验过程:

注意:

本实验都是range分区

准备基表,id列作为分区key

create table part_test(id number,name varchar2(10))

partition by range (id)

(

partition p1 values less than (5),

partition p2 values less than (10),

partition p3 values less than (15)

);

 

SQL> select table_name,partition_name,HIGH_VALUE from dba_tab_partitions where table_name='PART_TEST' order by table_name,partition_name;

 

TABLE_NAME                                                PARTITION_NAME                               HIGH_VALUE

------------------------------ ------------------------------ --------------------------------------------------------------------------------

PART_TEST                               P1                                                      5

PART_TEST                               P2                                                      10

PART_TEST                               P3                                                      15

 

 

 

一、准备基表及索引

 

globallocal信息不在同一个数据字典中;

global索引信息==>dba_indexes

local索引信息==>dba_ind_partitions/user_ind_partitions

*可以通过dba_indexes判断索引类型,如果status等于VALID或者UNUSABLE,索引类型是global;

status等于N/A,索引则是local索引。

 

 

1.建索引

创建global索引

create index ind_id on part_test(id);

 

创建local索引

create index ind_name on part_test(name) local;

 

PART_TEST上两个索引名、表名、及索引状态值

SQL> select INDEX_NAME,TABLE_OWNER,STATUS from dba_indexes where table_name='PART_TEST';

 

INDEX_NAME                                                TABLE_OWNER                                   STATUS

------------------------------ ------------------------------ --------

IND_ID                                                         DOWNLOAD                                       VALID  --global index

IND_NAME                               DOWNLOAD                                       N/A      --local index

 

 

2.索引状态值

查看global索引ind_id 信息

SQL> select INDEX_NAME,TABLE_OWNER,STATUS from dba_indexes where index_name='IND_ID';

 

INDEX_NAME                                                TABLE_OWNER                                   STATUS

------------------------------ ------------------------------ --------

IND_ID                                                         DOWNLOAD                                       VALID

 

查看local索引IND_NAME信息

SQL> select INDEX_NAME,PARTITION_NAME,HIGH_VALUE,STATUS from dba_ind_partitions where INDEX_NAME='IND_NAME';

 

INDEX_NAME                                                PARTITION_NAME                               HIGH_VALUE                                                                                                                                                   STATUS

------------------------------ ------------------------------ -------------------------------------------------------------------------------- --------

IND_NAME                               P1                                                      5                                                                                                                                                                    USABLE

IND_NAME                               P2                                                      10                                                                                                                                                                   USABLE

IND_NAME                               P3                                                      15                                                                                                                                                                   USABLE

 

 

 

二、add partition

 

1.not plus "update indexes"

SQL> alter table part_test add partition p4 values less than (20);

 

Table altered.

 

索引都可用

SQL> select INDEX_NAME,TABLE_OWNER,STATUS from dba_indexes where index_name='IND_ID';

 

INDEX_NAME                                                TABLE_OWNER                                   STATUS

------------------------------ ------------------------------ --------

IND_ID                                                         DOWNLOAD                                       VALID

 

SQL>  select INDEX_NAME,PARTITION_NAME,HIGH_VALUE,STATUS from dba_ind_partitions where INDEX_NAME='IND_NAME';

 

INDEX_NAME                                                PARTITION_NAME                               HIGH_VALUE                                                                                                                                                   STATUS

------------------------------ ------------------------------ -------------------------------------------------------------------------------- --------

IND_NAME                               P1                                                      5                                                                                                                                                                    USABLE

IND_NAME                               P2                                                      10                                                                                                                                                                   USABLE

IND_NAME                               P3                                                      15                                                                                                                                                                   USABLE

IND_NAME                               P4                                                      20        

 

 

2. plus  "update indexes"

SQL>  alter table part_test add partition p5 values less than (25) update indexes;

 

Table altered.

 

索引同样都可用

SQL> select INDEX_NAME,TABLE_OWNER,STATUS from dba_indexes where index_name='IND_ID';

 

INDEX_NAME                                                TABLE_OWNER                                   STATUS

------------------------------ ------------------------------ --------

IND_ID                                                         DOWNLOAD                                       VALID

 

SQL>  select INDEX_NAME,PARTITION_NAME,HIGH_VALUE,STATUS from dba_ind_partitions where INDEX_NAME='IND_NAME';

 

INDEX_NAME                                                PARTITION_NAME                               HIGH_VALUE                                                                                                                                                   STATUS

------------------------------ ------------------------------ -------------------------------------------------------------------------------- --------

IND_NAME                               P1                                                      5                                                                                                                                                                    USABLE

IND_NAME                               P2                                                      10                                                                                                                                                                   USABLE

IND_NAME                               P3                                                      15                                                                                                                                                                   USABLE

IND_NAME                               P4                                                      20                                                                                                                                                                   USABLE

IND_NAME                               P5                                                      25                                                                                                                                                                   USABLE

 

 

结论:add partition时,不加update indexes不会破坏localglobal索引的可用性;当然,加上update indexes更好更保险。

 

 

 

三、drop partition

 

1.删除没有数据的分区

<1>not plus "update indexes" ,同时分区中没有任何数据

SQL> alter table part_test drop partition p4;

 

Table altered.

 

删除分区后,索引依然可用。

SQL>  select INDEX_NAME,TABLE_OWNER,STATUS from dba_indexes where index_name='IND_ID';

 

INDEX_NAME                                                TABLE_OWNER                                   STATUS

------------------------------ ------------------------------ --------

IND_ID                                                         DOWNLOAD                                       VALID

 

SQL>  select INDEX_NAME,PARTITION_NAME,HIGH_VALUE,STATUS from dba_ind_partitions where INDEX_NAME='IND_NAME';

 

INDEX_NAME                                                PARTITION_NAME                               HIGH_VALUE                                                                                                                                                   STATUS

------------------------------ ------------------------------ -------------------------------------------------------------------------------- --------

IND_NAME                               P1                                                      5                                                                                                                                                                    USABLE

IND_NAME                               P2                                                      10                                                                                                                                                                   USABLE

IND_NAME                               P3                                                      15                                                                                                                                                                   USABLE

IND_NAME                               P5                                                      25                                                                                                                                                                   USABLE

 

<2>plus "update indexes" ,同时分区中没有任何数据

SQL> alter table part_test drop partition p5 update indexes;

 

Table altered.

 

删除分区后,索引依然可用。

SQL>   select INDEX_NAME,TABLE_OWNER,STATUS from dba_indexes where index_name='IND_ID';

 

INDEX_NAME                                                TABLE_OWNER                                   STATUS

------------------------------ ------------------------------ --------

IND_ID                                                         DOWNLOAD                                       VALID

 

SQL>  select INDEX_NAME,PARTITION_NAME,HIGH_VALUE,STATUS from dba_ind_partitions where INDEX_NAME='IND_NAME';

 

INDEX_NAME                                                PARTITION_NAME                               HIGH_VALUE                                                                                                                                                   STATUS

------------------------------ ------------------------------ -------------------------------------------------------------------------------- --------

IND_NAME                               P1                                                      5                                                                                                                                                                    USABLE

IND_NAME                               P2                                                      10                                                                                                                                                                   USABLE

IND_NAME                               P3                                                      15                                                                                                                                                                   USABLE

 

小结:分区中没有数据,不加update index drop该索引也不会引起localglobal索引失效。

 

2.删除含有数据的分区

insert into part_test values(1,'tom');   --落在p1分区

insert into part_test values(6,'lucy');   --落在p2分区

insert into part_test values(11,'lucy'); --落在p3分区

 

SQL> select * from part_test partition(p1);

 

                    ID NAME

---------- ----------

                     1 tom

 

SQL>

SQL>  select * from part_test partition(p2);

 

                    ID NAME

---------- ----------

                     6 lucy

 

SQL>  select * from part_test partition(p3);

 

                    ID NAME

---------- ----------

                    11 lucy

 

 

<1>not plus "update indexes"

SQL> alter table part_test drop partition p3;

 

Table altered.

 

global索引失效

SQL> select INDEX_NAME,TABLE_OWNER,STATUS from dba_indexes where index_name='IND_ID';

 

INDEX_NAME                                                TABLE_OWNER                                   STATUS

------------------------------ ------------------------------ --------

IND_ID                                                         DOWNLOAD                                       UNUSABLE

 

local索引正常

SQL>   select INDEX_NAME,PARTITION_NAME,HIGH_VALUE,STATUS from dba_ind_partitions where INDEX_NAME='IND_NAME';

 

INDEX_NAME                                                PARTITION_NAME                               HIGH_VALUE                                                                                                                                                   STATUS

------------------------------ ------------------------------ -------------------------------------------------------------------------------- --------

IND_NAME                               P1                                                      5                                                                                                                                                                    USABLE

IND_NAME                               P2                                                      10                                                                                                                                                                   USABLE

 

 

小结:分区中含有数据,删除分区会造成global索引失效;local 索引正常。

 

 

<2>plus "update indexes"

首先修复global索引IND_ID,通过rebuild

alter index IND_ID rebuild;

 

再添加几个实验分区

alter table part_test add partition p2 values less than(10);

alter table part_test add partition p3 values less than(15);

alter table part_test add partition p4 values less than(20);

 

insert into part_test values(6,'lucy');   --落在p2分区

insert into part_test values(11,'lucy'); --落在p3分区

 

SQL> alter table part_test drop partition p2 update indexes;

 

Table altered.

 

SQL> select INDEX_NAME,TABLE_OWNER,STATUS from dba_indexes where index_name='IND_ID';

 

INDEX_NAME                                                TABLE_OWNER                                   STATUS

------------------------------ ------------------------------ --------

IND_ID                                                         DOWNLOAD                                       VALID

 

SQL> select INDEX_NAME,PARTITION_NAME,HIGH_VALUE,STATUS from dba_ind_partitions where INDEX_NAME='IND_NAME';

 

INDEX_NAME                                                PARTITION_NAME                               HIGH_VALUE                                                                                                                                                   STATUS

------------------------------ ------------------------------ -------------------------------------------------------------------------------- --------

IND_NAME                               P1                                                      5                                                                                                                                                                    USABLE

IND_NAME                               P3                                                      15                                                                                                                                                                   USABLE

IND_NAME                               P4                                                      20                                                                                                                                                                   USABLE

 

小结:分区中含有数据,不加update indexes,会造成global索引失效,而local索引不受影响。

 

 

四、move partition

1.分区含有数据,不加update indexes

SQL> alter table part_test move partition p3;

 

Table altered.

 

global索引失效

SQL> select INDEX_NAME,TABLE_OWNER,STATUS from dba_indexes where index_name='IND_ID';

 

INDEX_NAME                                                TABLE_OWNER                                   STATUS

------------------------------ ------------------------------ --------

IND_ID                                                         DOWNLOAD                                       UNUSABLE

 

分区对应的local索引失效,而其他分区的local索引不受影响。

SQL>  select INDEX_NAME,PARTITION_NAME,HIGH_VALUE,STATUS from dba_ind_partitions where INDEX_NAME='IND_NAME';

 

INDEX_NAME                                                PARTITION_NAME                               HIGH_VALUE                                                                                                                                                   STATUS

------------------------------ ------------------------------ -------------------------------------------------------------------------------- --------

IND_NAME                               P1                                                      5                                                                                                                                                                    USABLE

IND_NAME                               P3                                                      15                                                                                                                                                                   UNUSABLE

IND_NAME                               P4                                                      20                                                                                                                                                                   USABLE

 

恢复失效索引

global 索引

SQL> alter index IND_ID rebuild;

 

Index altered.

 

local索引:rebuild 失效的lcoal索引即可。

SQL> alter index IND_NAME rebuild partition p3;

 

Index altered.

 

SQL> select INDEX_NAME,PARTITION_NAME,HIGH_VALUE,STATUS from dba_ind_partitions where INDEX_NAME='IND_NAME';

 

INDEX_NAME                                                PARTITION_NAME                               HIGH_VALUE                                                                                                                                                   STATUS

------------------------------ ------------------------------ -------------------------------------------------------------------------------- --------

IND_NAME                               P1                                                      5                                                                                                                                                                    USABLE

IND_NAME                               P3                                                      15                                                                                                                                                                   USABLE

IND_NAME                               P4                                                      20                                                                                                                                                                   USABLE

 

 

2.分区含有数据,加update indexes

SQL> alter table part_test move partition p3 update indexes; 

 

Table altered.

 

加上update indexes后,两种索引都正常。

SQL> select INDEX_NAME,TABLE_OWNER,STATUS from dba_indexes where index_name='IND_ID';

 

INDEX_NAME                                                TABLE_OWNER                                   STATUS

------------------------------ ------------------------------ --------

IND_ID                                                         DOWNLOAD                                       VALID

 

SQL> select INDEX_NAME,PARTITION_NAME,HIGH_VALUE,STATUS from dba_ind_partitions where INDEX_NAME='IND_NAME';

 

INDEX_NAME                                                PARTITION_NAME                               HIGH_VALUE                                                                                                                                                   STATUS

------------------------------ ------------------------------ -------------------------------------------------------------------------------- --------

IND_NAME                               P1                                                      5                                                                                                                                                                    USABLE

IND_NAME                               P3                                                      15                                                                                                                                                                   USABLE

IND_NAME                               P4                                                      20                                                                                                                                                                   USABLE

 

小结:分区中含有数据,move分区会造成global和分区对应local索引失效;加上update indexes参数能够避免这个问题。

 

 

五、truncate partition

1.分区含有数据,不加update indexes

SQL> alter table part_test truncate partition p3;

 

Table truncated.

 

global索引失效不可用

SQL>  select INDEX_NAME,TABLE_OWNER,STATUS from dba_indexes where index_name='IND_ID';

 

INDEX_NAME                                                TABLE_OWNER                                   STATUS

------------------------------ ------------------------------ --------

IND_ID                                                         DOWNLOAD                                       UNUSABLE

 

 

local索引依然可用

SQL> select INDEX_NAME,PARTITION_NAME,HIGH_VALUE,STATUS from dba_ind_partitions where INDEX_NAME='IND_NAME';

 

INDEX_NAME                                                PARTITION_NAME                               HIGH_VALUE                                                                                                                                                   STATUS

------------------------------ ------------------------------ -------------------------------------------------------------------------------- --------

IND_NAME                               P1                                                      5                                                                                                                                                                    USABLE

IND_NAME                              P3                                                      15                                                                                                                                                                   USABLE

IND_NAME                               P4                                                      20                                                                                                                                                                   USABLE

 

 

2.分区含有数据,加update indexes

SQL> insert into part_test values(11,'lucy');  --落在p3分区

 

1 row created.

 

SQL> commit;

 

Commit complete.

 

重建global索引

SQL> alter index IND_ID rebuild;

 

Index altered.

 

 

SQL> alter table part_test truncate partition p3 update indexes;

 

Table truncated.

 

 

加上update indexes后,global索引不再失效。

SQL> select INDEX_NAME,TABLE_OWNER,STATUS from dba_indexes where index_name='IND_ID';

 

INDEX_NAME                                                TABLE_OWNER                                   STATUS

------------------------------ ------------------------------ --------

IND_ID                                                         DOWNLOAD                                       VALID

 

SQL>  select INDEX_NAME,PARTITION_NAME,HIGH_VALUE,STATUS from dba_ind_partitions where INDEX_NAME='IND_NAME';

 

INDEX_NAME                                                PARTITION_NAME                               HIGH_VALUE                                                                                                                                                   STATUS

------------------------------ ------------------------------ -------------------------------------------------------------------------------- --------

IND_NAME                               P1                                                      5                                                                                                                                                                    USABLE

IND_NAME                               P3                                                      15                                                                                                                                                                   USABLE

IND_NAME                               P4                                                      20                                                                                                                                                                   USABLE

 

小结:分区表中含有数据,truncate partition会造成global索引失效;truncate partition加上update indexes可用避免这种情况。

 

 

六、SPLIT PARTITION(一个分区分裂为多个分区)

准备实验分区

 

SQL> alter table part_test add partition max_part values less than(maxvalue);

 

Table altered.

 

SQL> insert into part_test values(21,'john');

 

1 row created.

 

SQL>  insert into part_test values(30,'dog');

 

1 row created.

 

SQL> commit;

 

Commit complete.

 

 

SQL>  select * from part_test partition(MAX_PART);

 

                    ID NAME

---------- ----------

                    21 john

                    30 dog

 

 

1.不加update indexes

SQL> alter table part_test split partition max_part at (25) into (partition p5,partition max_part );

 

Table altered.

 

global索引不可用

SQL>  select INDEX_NAME,TABLE_OWNER,STATUS from dba_indexes where index_name='IND_ID';

 

INDEX_NAME                                                TABLE_OWNER                                   STATUS

------------------------------ ------------------------------ --------

IND_ID                                                         DOWNLOAD                                       UNUSABLE

 

 

local索引,原分区和分裂出的新分区都不可用。

SQL> select INDEX_NAME,PARTITION_NAME,HIGH_VALUE,STATUS from dba_ind_partitions where INDEX_NAME='IND_NAME';

 

INDEX_NAME                                                PARTITION_NAME                               HIGH_VALUE                                                                                                                                                   STATUS

------------------------------ ------------------------------ -------------------------------------------------------------------------------- --------

IND_NAME                               P1                                                      5                                                                                                                                                                    USABLE

IND_NAME                               P3                                                      15                                                                                                                                                                   USABLE

IND_NAME                               P4                                                      20                                                                                                                                                                   USABLE

IND_NAME                               P5                                                      25                                                                                                                                                                   UNUSABLE

IND_NAME                               MAX_PART                                         MAXVALUE                                                                                                                                                                           UNUSABLE

 

 

2.update indexes

 

SQL> select INDEX_NAME,PARTITION_NAME,HIGH_VALUE,STATUS from dba_ind_partitions where INDEX_NAME='IND_NAME';

 

INDEX_NAME                                                PARTITION_NAME                               HIGH_VALUE                                                                                                                                                   STATUS

------------------------------ ------------------------------ -------------------------------------------------------------------------------- --------

IND_NAME                               P1                                                      5                                                                                                                                                                     USABLE

IND_NAME                               P3                                                      15                                                                                                                                                                   USABLE

IND_NAME                               P4                                                      20                                                                                                                                                                   USABLE

IND_NAME                               MAX_PART                                         MAXVALUE                                                                                                                                                                           USABLE

 

SQL> alter table part_test split partition max_part at (25) into (partition p5,partition max_part ) update indexes;

 

Table altered.

 

update indexes后,分裂不会造成索引失效。

SQL> select INDEX_NAME,TABLE_OWNER,STATUS from dba_indexes where index_name='IND_ID';

 

INDEX_NAME                                                TABLE_OWNER                                   STATUS

------------------------------ ------------------------------ --------

IND_ID                                                         DOWNLOAD                                       VALID

 

SQL>  select INDEX_NAME,PARTITION_NAME,HIGH_VALUE,STATUS from dba_ind_partitions where INDEX_NAME='IND_NAME';

 

INDEX_NAME                                                PARTITION_NAME                               HIGH_VALUE                                                                                                                                                  STATUS

------------------------------ ------------------------------ -------------------------------------------------------------------------------- --------

IND_NAME                               P1                                                      5                                                                                                                                                                    USABLE

IND_NAME                               P3                                                      15                                                                                                                                                                   USABLE

IND_NAME                               P4                                                      20                                                                                                                                                                   USABLE

IND_NAME                               P5                                                      25                                                                                                                                                                   USABLE

IND_NAME                               MAX_PART                                         MAXVALUE                                                                                                                                                                           USABLE

 

小结:split partition操作,会使global索引和local索引的原分区和分裂出的新分区都不可用。加上update indexes解决这个问题。

 

 

 

七、merge partition(合并分区)

现有分区

SQL>  select table_name,partition_name,HIGH_VALUE from dba_tab_partitions where table_name='PART_TEST' order by  PARTITION_NAME;

 

TABLE_NAME                                                PARTITION_NAME                               HIGH_VALUE

------------------------------ ------------------------------ --------------------------------------------------------------------------------

PART_TEST                               MAX_PART                                         MAXVALUE

PART_TEST                               P1                                                      5

PART_TEST                               P3                                                      15

PART_TEST                               P4                                                      20

PART_TEST                               P5                                                      25

 

 

 

p4p5分区都含有数据,把他们合并为p6分区

 

1.不加update indexes

SQL>  alter table PART_TEST merge partitions p4,p5 into partition p6;

 

Table altered.

 

 

global索引失效

SQL> select INDEX_NAME,TABLE_OWNER,STATUS from dba_indexes where index_name='IND_ID';

 

INDEX_NAME                                                TABLE_OWNER                                   STATUS

------------------------------ ------------------------------ --------

IND_ID                                                         DOWNLOAD                                       UNUSABLE

 

新生成的分区的索引是失效的。

SQL>  select INDEX_NAME,PARTITION_NAME,HIGH_VALUE,STATUS from dba_ind_partitions where INDEX_NAME='IND_NAME';

 

INDEX_NAME                                                PARTITION_NAME                               HIGH_VALUE                                                                                                                                                   STATUS

------------------------------ ------------------------------ -------------------------------------------------------------------------------- --------

IND_NAME                               P1                                                      5                                                                                                                                                                    USABLE

IND_NAME                               P3                                                      15                                                                                                                                                                   USABLE

IND_NAME                               P6                                                      25                                                                                                                                                                   UNUSABLE

IND_NAME                               MAX_PART                                         MAXVALUE                                                                                                                                                                           USABLE

 

 

2.update indexes

SQL> alter table PART_TEST merge partitions p4,p5 into partition p6 update indexes;

 

 

Table altered.

 

 

globallocal索引都正常。

SQL> SQL>  select INDEX_NAME,TABLE_OWNER,STATUS from dba_indexes where index_name='IND_ID';

 

INDEX_NAME                                                TABLE_OWNER                                   STATUS

------------------------------ ------------------------------ --------

IND_ID                                                         DOWNLOAD                                       VALID

 

SQL>  select INDEX_NAME,PARTITION_NAME,HIGH_VALUE,STATUS from dba_ind_partitions where INDEX_NAME='IND_NAME';

 

INDEX_NAME                                                PARTITION_NAME                               HIGH_VALUE                                                                                                                                                   STATUS

------------------------------ ------------------------------ -------------------------------------------------------------------------------- --------

IND_NAME                               P1                                                      5                                                                                                                                                                    USABLE

IND_NAME                               P3                                                      15                                                                                                                                                                   USABLE

IND_NAME                               P6                                                      25                                                                                                                                                                   USABLE

IND_NAME                               MAX_PART                                         MAXVALUE                                                                                                                                                                           USABLE

 

 

八、COALESCE PARTITION (收缩表分区)

仅能应用在hash 分区表,执行一次就少一个分区,如一个hash分区表含有3个分区,COALESCE PARTITION 一次就成了2个分区。

create table part_hash(id number,name nvarchar2(20))

partition by hash(id)

(

partition p1,

partition p2,

partition p3,

partition p4

);

 

 

create index ind_hash_id on part_hash(id);

create index ind_hash_name on part_hash(name) local;

 

insert into part_hash values(1,'tom');

insert into part_hash values(2,'lucy');

insert into part_hash values(3,'john');

insert into part_hash values(4,'kate');

 

 索引信息

SQL> select INDEX_NAME,TABLE_OWNER,STATUS from dba_indexes where index_name='IND_HASH_ID';

 

INDEX_NAME                                                TABLE_OWNER                                   STATUS

------------------------------ ------------------------------ --------

IND_HASH_ID                                               DOWNLOAD                                       VALID

 

SQL>  select INDEX_NAME,PARTITION_NAME,HIGH_VALUE,STATUS from dba_ind_partitions where INDEX_NAME='IND_HASH_NAME';

 

INDEX_NAME                                                PARTITION_NAME                               HIGH_VALUE                                                                                                                                                   STATUS

------------------------------ ------------------------------ -------------------------------------------------------------------------------- --------

IND_HASH_NAME                                         P1                                                                                                                                                                                                                                                   USABLE

IND_HASH_NAME                                         P2                                                                                                                                                                                                                                                   USABLE

IND_HASH_NAME                                         P3                                                                                                                                                                                                                                                   USABLE

IND_HASH_NAME                                         P4                                                                                                                                                                                                                                                   USABLE

 

表信息

SQL>  select table_name,partition_name,HIGH_VALUE from dba_tab_partitions where table_name='PART_HASH' order by table_name,partition_name;

 

TABLE_NAME                                                PARTITION_NAME                               HIGH_VALUE

------------------------------ ------------------------------ --------------------------------------------------------------------------------

PART_HASH                             P1

PART_HASH                             P2

PART_HASH                             P3

PART_HASH                             P4

 

1.下面进行coalesce partiton操作,不加update index

SQL> alter table PART_HASH coalesce partition;

 

Table altered.

 

操作引起globallocal索引失效。

SQL> select INDEX_NAME,TABLE_OWNER,STATUS from dba_indexes where index_name='IND_HASH_ID';

 

INDEX_NAME                                                TABLE_OWNER                                   STATUS

------------------------------ ------------------------------ --------

IND_HASH_ID                                               DOWNLOAD                                       UNUSABLE

 

SQL>  select INDEX_NAME,PARTITION_NAME,HIGH_VALUE,STATUS from dba_ind_partitions where INDEX_NAME='IND_HASH_NAME';

 

INDEX_NAME                                                PARTITION_NAME                               HIGH_VALUE                                                                                                                                                   STATUS

------------------------------ ------------------------------ -------------------------------------------------------------------------------- --------

IND_HASH_NAME                                         P1                                                                                                                                                                                                                                                   USABLE

IND_HASH_NAME                                         P2                                                                                                                                                                                                                                                   UNUSABLE

IND_HASH_NAME                                         P3                                                                                                                                                                                                                                                   USABLE

 

 

修复索引

alter index IND_HASH_ID rebuild;

alter index IND_HASH_NAME rebuild partition p2;

 

2.update indexes

SQL>  alter table PART_HASH coalesce partition update indexes;

 

Table altered.

 

 

操作后,索引没有失效。

SQL> select INDEX_NAME,TABLE_OWNER,STATUS from dba_indexes where index_name='IND_HASH_ID';

 

INDEX_NAME                                                TABLE_OWNER                                   STATUS

------------------------------ ------------------------------ --------

IND_HASH_ID                                               DOWNLOAD                                       VALID

 

SQL> select INDEX_NAME,PARTITION_NAME,HIGH_VALUE,STATUS from dba_ind_partitions where INDEX_NAME='IND_HASH_NAME';

 

INDEX_NAME                                                PARTITION_NAME                               HIGH_VALUE                                                                                                                                                   STATUS

------------------------------ ------------------------------ -------------------------------------------------------------------------------- --------

IND_HASH_NAME                                         P1                                                                                                                                                                                                                                                   USABLE

IND_HASH_NAME                                         P2                                                                                                                                                                                                                                                   USABLE

 

 

九、exchange partition

exchange partition是表与表,表与分区,分区与分区数据交换的命令,不是将表转换成分区,或者分区转换成表

依然用上面的part_test分区表

SQL> select * from PART_TEST partition(p6);

 

                    ID NAME

---------- ----------

                    18 ok

                    21 john

                    21 jodan

 

 

新建一个表exchange_test

create table exchange_test(id number,name varchar2(10));

 

下面把PART_TEST partition(p6)中的数据exchangeexchange_test

SQL> alter table PART_TEST exchange partition p6 with table exchange_test;

 

Table altered.

 

p6分区没有数据了

SQL> select * from PART_TEST partition(p6);

 

no rows selected

 

数据跑到了exchange_test中。

SQL> select * from exchange_test;

 

                    ID NAME

---------- ----------

                    18 ok

                    21 john

                    21 jodan

 

 

看分区表索引情况,global索引失效,p6上的local索引失效。

SQL>  select INDEX_NAME,TABLE_OWNER,STATUS from dba_indexes where index_name='IND_ID';

 

INDEX_NAME                                                TABLE_OWNER                                   STATUS

------------------------------ ------------------------------ --------

IND_ID                                                         DOWNLOAD                                       UNUSABLE

 

SQL>  select INDEX_NAME,PARTITION_NAME,HIGH_VALUE,STATUS from dba_ind_partitions where INDEX_NAME='IND_NAME';

 

INDEX_NAME                                                PARTITION_NAME                               HIGH_VALUE                                                                                                                                                   STATUS

------------------------------ ------------------------------ -------------------------------------------------------------------------------- --------

IND_NAME                               P1                                                      5                                                                                                                                                                    USABLE

IND_NAME                               P3                                                      15                                                                                                                                                                   USABLE

IND_NAME                               P6                                                      25                                                                                                                                                                   UNUSABLE

IND_NAME                               MAX_PART                                         MAXVALUE                                                                                                                                                                           USABLE

 

 

恢复索引

alter index IND_ID rebuild;

alter index IND_NAME rebuild partition p6;

 

2.update indexes

SQL> alter table PART_TEST exchange partition p6 with table exchange_test update indexes;

 

数据又回去了

SQL> select * from exchange_test;

 

no rows selected

 

SQL> select * from PART_TEST partition(p6);

 

                    ID NAME

---------- ----------

                    18 ok

                    21 john

                    21 jodan

 

 

看索引情况

global索引正常

SQL>   select INDEX_NAME,TABLE_OWNER,STATUS from dba_indexes where index_name='IND_ID';

 

INDEX_NAME                                                TABLE_OWNER                                   STATUS

------------------------------ ------------------------------ --------

IND_ID                                                         DOWNLOAD                                       VALID

 

local索引还是失效了。

SQL>  select INDEX_NAME,PARTITION_NAME,HIGH_VALUE,STATUS from dba_ind_partitions where INDEX_NAME='IND_NAME';

 

INDEX_NAME                                                PARTITION_NAME                               HIGH_VALUE                                                                                                                                                   STATUS

------------------------------ ------------------------------ -------------------------------------------------------------------------------- --------

IND_NAME                               P1                                                      5                                                                                                                                                                    USABLE

IND_NAME                               P3                                                      15                                                                                                                                                                   USABLE

IND_NAME                               P6                                                      25                                                                                                                                                                   UNUSABLE

IND_NAME                               MAX_PART                                         MAXVALUE                                                                                                                                                                           USABLE

 

 

小结:加上update indexesglobal索引不失效,但是local 索引依然失效。

 

 

十、重命名分区和local索引名字都不会影响索引状态

SQL> alter table part_test rename partition P6 to p5;

SQL> alter index IND_NAME rename partition P6 to p5;

 

 

 

 

 

注意:

local索引不能整个rebuild,要rebuild索引分区。

SQL> alter index IND_NAME rebuild;

alter index IND_NAME rebuild

            *

ERROR at line 1:

ORA-14086: a partitioned index may not be rebuilt as a whole

 

 

SQL> alter index IND_NAME rebuild partition MAX_PART;

 

Index altered.

 

 

如果local索引大面积不可用,那么用动态sql生成重语句吧

SQL> select 'alter index INDEX_NAME rebuild partition '||PARTITION_NAME||';' from user_ind_partitions where INDEX_NAME='INDEX_NAME' and STATUS='UNUSABLE';

 

 

阅读(9711) | 评论(3) | 转发(1) |
给主人留下些什么吧!~~

wittzhang2015-08-11 10:44:58

写的真详细,学习了。感谢分享。

十字螺丝钉2012-12-10 10:00:26

vcdog: 纸上得来终觉浅,绝知此事要躬行。学习oracle就要多做实验,才能更深入地理解它。楼主写得很棒,加油!.....
多谢鼓励

vcdog2012-12-09 22:45:16

纸上得来终觉浅,绝知此事要躬行。学习oracle就要多做实验,才能更深入地理解它。楼主写得很棒,加油!

评论热议
请登录后评论。

登录 注册