官网截图：
global partition index
local partition index
《全局分区索引》
drop table JS_SKSKJDK_DZFPMX02 purge;--创建分区表 jssjSQL> create table JS_SKSKJDK_DZFPMX02( fpdm VARCHAR2(12) not null, fphm VARCHAR2(8) not null, jssj DATE, CONSTRAINT PK_JS_SKSKJDK_DZFPMX02 PRIMARY KEY(FPHM,FPDM) USING INDEX TABLESPACE users ) PARTITION BY RANGE(JSSJ) (PARTITION p01 VALUES LESS THAN(to_date('2016/8/31 10:53:40','yyyy-mm-dd hh24:mi:ss')) TABLESPACE users) enable row movement; Table created.--创建全局分区索引drop index ind_jssj02;SQL> create index ind_jssj02 on JS_SKSKJDK_DZFPMX02(jssj) global partition by range (jssj) ( partition p02 values less than (to_date('2016/10/30 10:53:40','yyyy-mm-dd hh24:mi:ss')) , partition p03 values less than (to_date('2016/11/30 10:53:40','yyyy-mm-dd hh24:mi:ss')), partition p04 values less than (to_date('2016/12/3 10:53:40','yyyy-mm-dd hh24:mi:ss')), partition p05 values less than (to_date('2016/12/30 10:53:40','yyyy-mm-dd hh24:mi:ss')), partition pmax values less than (maxvalue) ) ; Index created.-- 为分区表新增分区 SQL> alter table JS_SKSKJDK_DZFPMX02 add partition p02 values less than (to_date('2016/10/30 10:53:40','yyyy-mm-dd hh24:mi:ss')) tablespace users;Table altered.SQL> alter table JS_SKSKJDK_DZFPMX02 add partition p03 values less than (to_date('2016/11/30 10:53:40','yyyy-mm-dd hh24:mi:ss')) tablespace users;Table altered.SQL> alter table JS_SKSKJDK_DZFPMX02 add partition p04 values less than (to_date('2016/12/3 10:53:40','yyyy-mm-dd hh24:mi:ss')) tablespace users; Table altered.SQL> alter table JS_SKSKJDK_DZFPMX02 add partition p05 values less than (to_date('2016/12/30 10:53:40','yyyy-mm-dd hh24:mi:ss')) tablespace users; Table altered.-- 将全局分区索引重建于指定的索引分区alter index ind_jssj02 rebuild partition p04 tablespace users;--分区表插入测试数据SQL> insert into JS_SKSKJDK_DZFPMX02 values('1','1',sysdate);1 row created.SQL> insert into JS_SKSKJDK_DZFPMX02 values('2','2',sysdate);1 row created.SQL> insert into JS_SKSKJDK_DZFPMX02 values('3','3',sysdate);1 row created.--检查索引状态 SQL> select t2.locality,t1.index_name,t1.partition_name,t1.status,t1.tablespace_name from user_ind_partitions t1, user_part_indexes t2 where t1.index_name in ('IND_JSSJ02') and t1.index_name=t2.index_name order by index_name,partition_name;LOCALI INDEX_NAME PARTITION_NAME STATUS TABLESPACE_NAME------ ------------------------------ ------------------------------ -------- ------------------------------GLOBAL IND_JSSJ02 P02 USABLE USERSGLOBAL IND_JSSJ02 P03 USABLE USERSGLOBAL IND_JSSJ02 P04 USABLE USERSGLOBAL IND_JSSJ02 P05 USABLE USERSGLOBAL IND_JSSJ02 PMAX USABLE USERS--删除某个分区SQL> alter table JS_SKSKJDK_DZFPMX02 drop partition p02;Table altered.--再次检查索引状态 SQL> select t2.locality,t1.index_name,t1.partition_name,t1.status,t1.tablespace_name from user_ind_partitions t1, user_part_indexes t2 where t1.index_name in ('IND_JSSJ02') and t1.index_name=t2.index_name order by index_name,partition_name;LOCALI INDEX_NAME PARTITION_NAME STATUS TABLESPACE_NAME------ ------------------------------ ------------------------------ -------- ------------------------------GLOBAL IND_JSSJ02 P02 UNUSABLE USERSGLOBAL IND_JSSJ02 P03 UNUSABLE USERSGLOBAL IND_JSSJ02 P04 UNUSABLE USERSGLOBAL IND_JSSJ02 P05 UNUSABLE USERSGLOBAL IND_JSSJ02 PMAX UNUSABLE USERS--结论： 全局分区索引果然不会自动维护分区索引啊
《本地分区索引》
drop table JS_SKSKJDK_DZFPMX01 purge;--创建分区表 jssjSQL> create table JS_SKSKJDK_DZFPMX01( fpdm VARCHAR2(12) not null, fphm VARCHAR2(8) not null, jssj DATE, CONSTRAINT PK_JS_SKSKJDK_DZFPMX01 PRIMARY KEY(FPHM,FPDM) USING INDEX TABLESPACE users ) PARTITION BY RANGE(JSSJ) (PARTITION p01 VALUES LESS THAN(to_date('2016/8/31 10:53:40','yyyy-mm-dd hh24:mi:ss')) TABLESPACE users) enable row movement;Table created.--创建本地分区索引SQL> create index ind_jssj01 on JS_SKSKJDK_DZFPMX01(jssj) local tablespace users; Index created.-- 为分区表新增分区 SQL> alter table JS_SKSKJDK_DZFPMX01 add partition p02 values less than (to_date('2016/10/30 10:53:40','yyyy-mm-dd hh24:mi:ss')) tablespace users;Table altered.SQL> alter table JS_SKSKJDK_DZFPMX01 add partition p03 values less than (to_date('2016/11/30 10:53:40','yyyy-mm-dd hh24:mi:ss')) tablespace users;Table altered.SQL> alter table JS_SKSKJDK_DZFPMX01 add partition p04 values less than (to_date('2016/12/3 10:53:40','yyyy-mm-dd hh24:mi:ss')) tablespace users;Table altered.SQL> alter table JS_SKSKJDK_DZFPMX01 add partition p05 values less than (to_date('2016/12/30 10:53:40','yyyy-mm-dd hh24:mi:ss')) tablespace users;Table altered.-- 将局部索引重建于指定的索引分区alter index ind_jssj rebuild partition part04 tablespace users;--检查索引状态 SQL> select t2.locality,t1.index_name,t1.partition_name,t1.status,t1.tablespace_name from user_ind_partitions t1, user_part_indexes t2 where t1.index_name in ('IND_JSSJ01') and t1.index_name=t2.index_name 2 order by index_name,partition_name;LOCALI INDEX_NAME PARTITION_NAME STATUS TABLESPACE_NAME------ ------------------------------ ------------------------------ -------- ------------------------------LOCAL IND_JSSJ01 P01 USABLE USERSLOCAL IND_JSSJ01 P02 USABLE USERSLOCAL IND_JSSJ01 P03 USABLE USERSLOCAL IND_JSSJ01 P04 USABLE USERSLOCAL IND_JSSJ01 P05 USABLE USERS--删除某个分区SQL> alter table JS_SKSKJDK_DZFPMX01 drop partition p02;Table altered.--再次检查索引状态 SQL> select t2.locality,t1.index_name,t1.partition_name,t1.status,t1.tablespace_name from user_ind_partitions t1, user_part_indexes t2 where t1.index_name in ('IND_JSSJ01') and t1.index_name=t2.index_name 2 order by index_name,partition_name;LOCALI INDEX_NAME PARTITION_NAME STATUS TABLESPACE_NAME------ ------------------------------ ------------------------------ -------- ------------------------------LOCAL IND_JSSJ01 P01 USABLE USERSLOCAL IND_JSSJ01 P03 USABLE USERSLOCAL IND_JSSJ01 P04 USABLE USERSLOCAL IND_JSSJ01 P05 USABLE USERS--结论 本地分区索引，删除某个分区 对于其他的索引分区没有任何影响
转载于:https://www.cnblogs.com/iyoume2008/p/5826145.html
相关资源：JAVA上百实例源码以及开源项目