简介

作为运维DBA，维护失效的分区索引是日常工作里时有发生的事情。那么，究其原因到底什么操作会导致分区表上的索引失效呢？今天我们就来做做的分析分区表全局与本地索引失效的测试

。从原理上来说，当数据没有发生位置上的实质变化，既某一行数据的rowid不变，索引扫描依然可以通过索引中存放的rowid定位到这行数据时，索引不会失效。这样的操作有添加和删除没有数据的partition（太明显了，不讨论）、split没有数据的partition。我们通过实验来看看吧！

1、搭测试环境

--查看数据文件路径

SQL> select name from v$datafile;

NAME
--------------------------------------------------------------------------------
/space/oradata/tx9ab/datafile/system01.dbf
/space/oradata/tx9ab/datafile/sysaux01.dbf
/space/oradata/tx9ab/datafile/undotbs01.dbf
/space/oradata/tx9ab/datafile/users01.dbf
/space/oradata/tx9ab/datafile/example01.dbf

--创建实验所用的分区表空间

SQL>
create tablespace test001 datafile '/space/oradata/tx9ab/datafile/test001.dbf' size 10m autoextend on;
create tablespace test002 datafile '/space/oradata/tx9ab/datafile/test002.dbf' size 10m autoextend on;
create tablespace test003 datafile '/space/oradata/tx9ab/datafile/test003.dbf' size 10m autoextend on;
create tablespace test004 datafile '/space/oradata/tx9ab/datafile/test004.dbf' size 10m autoextend on;
create tablespace test005 datafile '/space/oradata/tx9ab/datafile/test005.dbf' size 10m autoextend on;
create tablespace test006 datafile '/space/oradata/tx9ab/datafile/test006.dbf' size 10m autoextend on;

Tablespace created.

SQL>

Tablespace created.

SQL>
Tablespace created.

SQL>
Tablespace created.

SQL>
Tablespace created.

SQL>
Tablespace created.

SQL> select TS#,NAME from v$tablespace;

       TS# NAME
---------- ------------------------------
         0 SYSTEM
         1 SYSAUX
         2 UNDOTBS1
         4 USERS
         3 TEMP
         6 EXAMPLE
         7 TEST001
         8 TEST002
         9 TEST003
        10 TEST004
        11 TEST005

       TS# NAME
---------- ------------------------------
        12 TEST006

12 rows selected.

--创建测试表

SQL> create table pt partition by range(object_id)
(
partition p1 values less than (2000) tablespace test001,
partition p2 values less than (8000) tablespace test002,
partition p3 values less than (20000) tablespace test003,
partition p4 values less than (40000) tablespace test004,
partition pmax values less than (maxvalue) tablespace test005) as select * from dba_objects;

Table created.

--为测试表创建主键约束和本地索引

SQL> alter table pt add constraint pk_id primary key(object_id);

Table altered.

SQL>create index idx_pt on pt(object_name) local;

Index created.

2、查看当前索引状态

SQL> select index_name, status, last_analyzed, partitioned
from dba_indexes where index_name = 'PK_ID';

INDEX_NAME                     STATUS   LAST_ANALYZED                 PAR
------------------------------ -------- ----------------------------- ---
PK_ID                          VALID    10-AUG-2015 22:45:52                 NO

SQL> set linesize 200
SQL> col index_name for a25

select a.index_name, a.partition_name, a.tablespace_name, a.status
from dba_ind_partitions a where a.index_name = 'IDX_PT';

INDEX_NAME                PARTITION_NAME                 TABLESPACE_NAME                STATUS
------------------------- ------------------------------ ------------------------------ --------
IDX_PT                    P1                             TEST001                        USABLE
IDX_PT                    P2                             TEST002                        USABLE
IDX_PT                    P3                             TEST003                        USABLE
IDX_PT                    P4                             TEST004                        USABLE
IDX_PT                    PMAX                         TEST005                        USABLE

3、Split partition table

--查看split操作的零界点

SQL> select max(object_id) from pt partition(pmax);

MAX(OBJECT_ID)
--------------
         77009

a、数据位置不变的情况

--便于实验效果用77019，原先pmax中的数据依旧在test005中，新的pmax里没有数据

SQL> alter table pt split partition pmax at (77019) into (partition p5 tablespace test005,
                                                     partition pmax tablespace test006);

Table altered.

SQL> select a.index_name, a.partition_name, a.tablespace_name, a.status
from dba_ind_partitions a where index_name = 'IDX_PT';

INDEX_NAME                PARTITION_NAME                 TABLESPACE_NAME                STATUS
------------------------- ------------------------------ ------------------------------ --------
IDX_PT                    P1                             TEST001                        USABLE
IDX_PT                    P2                             TEST002                        USABLE
IDX_PT                    P3                             TEST003                        USABLE
IDX_PT                    P4                             TEST004                        USABLE
IDX_PT                    P5                             TEST005                        USABLE
IDX_PT                    PMAX                         TEST006                        USABLE

6 rows selected.

--split后本地索引仍然保持有效

SQL> select index_name, status, last_analyzed, partitioned
from dba_indexes where index_name = 'PK_ID';

INDEX_NAME                STATUS   LAST_ANALYZED                 PAR
------------------------- -------- ----------------------------- ---
PK_ID                     VALID    10-AUG-2015 22:45:52         NO

--As well as 全局索引

b、数据位置变化的情况

--还原实验环境.....忽略。

SQL> select max(object_id) from pt partition(pmax);

MAX(OBJECT_ID)
--------------
         77009

--从中间拆

SQL> alter table pt split partition pmax at (60000) into (partition p5 tablespace test005,
                                                               partition pmax tablespace test006);

Table altered.

--由于部分数据由test005-->test006，结果不言而喻

SQL> select a.index_name, a.partition_name, a.tablespace_name, a.status
from dba_ind_partitions a where a.index_name = 'IDX_PT';

INDEX_NAME                PARTITION_NAME                 TABLESPACE_NAME                STATUS
------------------------- ------------------------------ ------------------------------ --------
IDX_PT                    P1                             TEST001                        USABLE
IDX_PT                    P2                             TEST002                        USABLE
IDX_PT                    P3                             TEST003                        USABLE
IDX_PT                    P4                             TEST004                        USABLE
IDX_PT                    P5                             TEST005                        UNUSABLE
IDX_PT                    PMAX                        TEST006                        UNUSABLE

6 rows selected.

SQL> select index_name, status, last_analyzed, partitioned
from dba_indexes where index_name = 'PK_ID';

INDEX_NAME                STATUS   LAST_ANALYZED                 PAR
------------------------- -------- ----------------------------- ---
PK_ID                     UNUSABLE    10-AUG-2015 23:49:02          NO

--这个时候就需要对分区表上的索引进行必要的维护了

alter index pk_id rebuild online;
alter index idx_pt rebuild partition p5;
alter index idx_pt rebuild partition pmax;

SQL> select a.index_name, a.partition_name, a.tablespace_name, a.status
from dba_ind_partitions a where a.index_name = 'IDX_PT';

INDEX_NAME                PARTITION_NAME                 TABLESPACE_NAME                STATUS
------------------------- ------------------------------ ------------------------------ --------
IDX_PT                    P1                             TEST001                        USABLE
IDX_PT                    P2                             TEST002                        USABLE
IDX_PT                    P3                             TEST003                        USABLE
IDX_PT                    P4                             TEST004                        USABLE
IDX_PT                    P5                             TEST005                        USABLE
IDX_PT                    PMAX                        TEST006                        USABLE

6 rows selected.

SQL> select index_name, status, last_analyzed, partitioned
from dba_indexes where index_name = 'PK_ID';

INDEX_NAME                STATUS   LAST_ANALYZED                 PAR
------------------------- -------- ----------------------------- ---
PK_ID                             VALID    10-AUG-2015 23:53:12          NO

-- 注释
本地索引local index通过dba_indexes 查看的状态;
通过dba_ind_partitions来查看，可以看到每个索引分区的状态;

结论
      对分区表中实际数据进行了DDL的操作，都会造成分区表上的索引失效。

ITPub博客

分区表全局索引与本地索引失效测试

最新文章

ITPub博客

分区表 全局索引与本地索引失效测试

最新文章

分区表全局索引与本地索引失效测试