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

 简介

  作为运维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            

转载于:http://blog.itpub.net/26727294/viewspace-1767712/