是否可以只创建部分分区上的本地索引

可能在特定环境下,需要在分区表的部分分区上创建索引(减少创建索引时间),是否可能?

可以通过如下方式实现:
数据库版本:
SQL> select * from v$version;
 
BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
PL/SQL Release 11.2.0.3.0 - Production
CORE 11.2.0.3.0 Production
TNS for IBM/AIX RISC System/6000: Version 11.2.0.3.0 - Production
NLSRTL Version 11.2.0.3.0 - Production
 

1,创建分区表

CREATE TABLE range_part(
id NUMBER(10),
crt_date DATE NOT NULL
)
PARTITION BY RANGE(crt_date)
(
PARTITION yr0 VALUES LESS THAN (TO_DATE('20140101','YYYYMMDD')),
PARTITION yr1 VALUES LESS THAN (TO_DATE('20150101','YYYYMMDD')),
PARTITION yr2 VALUES LESS THAN (TO_DATE('20160101','YYYYMMDD')),
PARTITION yr3 VALUES LESS THAN (TO_DATE('20170101','YYYYMMDD')),
PARTITION yrm VALUES LESS THAN (MAXVALUE)
);

insert into range_part
select rownum id, add_months(to_date('20130101', 'YYYYMMDD'), rownum)
  from dual
connect by level < 100;
commit;

2,创建索引
2.1 创建UNUSABLE索引
CREATE INDEX idx_range_part ON range_part(id) LOCAL UNUSABLE;

SQL> select index_name,partitioned,visibility from user_indexes where index_name = 'IDX_RANGE_PART';
 
INDEX_NAME                     PARTITIONED VISIBILITY
------------------------------ ----------- ----------
IDX_RANGE_PART                 YES         VISIBLE
 
 
SQL> select index_name,partitioning_type,partition_count,locality from user_part_indexes where index_name = 'IDX_RANGE_PART';
 
INDEX_NAME                     PARTITIONING_TYPE PARTITION_COUNT LOCALITY
------------------------------ ----------------- --------------- --------
IDX_RANGE_PART                 RANGE                           5 LOCAL
 

SQL> select index_name,partition_name,status,segment_created from user_ind_partitions where index_name='IDX_RANGE_PART';
 
INDEX_NAME                     PARTITION_NAME                 STATUS   SEGMENT_CREATED
------------------------------ ------------------------------ -------- ---------------
IDX_RANGE_PART                 YR0                            UNUSABLE NO
IDX_RANGE_PART                 YR1                            UNUSABLE NO
IDX_RANGE_PART                 YR2                            UNUSABLE NO
IDX_RANGE_PART                 YR3                            UNUSABLE NO
IDX_RANGE_PART                 YRM                            UNUSABLE NO

2.2 重建分区索引
ALTER INDEX idx_range_part REBUILD PARTITION yr1;
ALTER INDEX idx_range_part REBUILD PARTITION yr2;

SQL> select index_name,partition_name,status,segment_created from user_ind_partitions where index_name='IDX_RANGE_PART';
 
INDEX_NAME                     PARTITION_NAME                 STATUS   SEGMENT_CREATED
------------------------------ ------------------------------ -------- ---------------
IDX_RANGE_PART                 YR0                            UNUSABLE NO
IDX_RANGE_PART                 YR1                            USABLE   YES
IDX_RANGE_PART                 YR2                            USABLE   YES
IDX_RANGE_PART                 YR3                            UNUSABLE NO
IDX_RANGE_PART                 YRM                            UNUSABLE NO

2.3 修改分区索引为UNUSABLE
ALTER INDEX idx_range_part MODIFY PARTITION yr1 UNUSABLE;
 
SQL> select index_name,partition_name,status,segment_created from user_ind_partitions where index_name='IDX_RANGE_PART';
 
INDEX_NAME                     PARTITION_NAME                 STATUS   SEGMENT_CREATED
------------------------------ ------------------------------ -------- ---------------
IDX_RANGE_PART                 YR0                            UNUSABLE NO
IDX_RANGE_PART                 YR1                            UNUSABLE NO
IDX_RANGE_PART                 YR2                            USABLE   YES
IDX_RANGE_PART                 YR3                            UNUSABLE NO
IDX_RANGE_PART                 YRM                            UNUSABLE NO

 

-------------------------10.2.1对比测试

数据库版本:
SQL>  select * from v$version;
 
BANNER
----------------------------------------------------------------
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Prod
PL/SQL Release 10.2.0.1.0 - Production
CORE 10.2.0.1.0 Production
TNS for Linux: Version 10.2.0.1.0 - Production
NLSRTL Version 10.2.0.1.0 - Production

效果相同,只是不支持延迟创建段和索引可见性（分别对应user_ind_partitions.SEGMENT_CREATED，user_indexes.visibility）。

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/18922393/viewspace-1069658/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/18922393/viewspace-1069658/