Oracle本地分区索引、全局本地分区索引

135次阅读

没有评论

共计 6074 个字符，预计需要花费 16 分钟才能阅读完成。

官网截图：

global partition index

Oracle 本地分区索引、全局本地分区索引

local partition index

Oracle 本地分区索引、全局本地分区索引

《全局分区索引》

drop table JS_SKSKJDK_DZFPMX02 purge;

– 创建分区表 jssj
SQL> 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 USERS
GLOBAL IND_JSSJ02 P03 USABLE USERS
GLOBAL IND_JSSJ02 P04 USABLE USERS
GLOBAL IND_JSSJ02 P05 USABLE USERS
GLOBAL 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 USERS
GLOBAL IND_JSSJ02 P03 UNUSABLE USERS
GLOBAL IND_JSSJ02 P04 UNUSABLE USERS
GLOBAL IND_JSSJ02 P05 UNUSABLE USERS
GLOBAL IND_JSSJ02 PMAX UNUSABLE USERS

– 结论：全局分区索引果然不会自动维护分区索引啊

《本地分区索引》

drop table JS_SKSKJDK_DZFPMX01 purge;

– 创建分区表 jssj
SQL> 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 USERS
LOCAL IND_JSSJ01 P02 USABLE USERS
LOCAL IND_JSSJ01 P03 USABLE USERS
LOCAL IND_JSSJ01 P04 USABLE USERS
LOCAL 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 USERS
LOCAL IND_JSSJ01 P03 USABLE USERS
LOCAL IND_JSSJ01 P04 USABLE USERS
LOCAL IND_JSSJ01 P05 USABLE USERS

– 结论本地分区索引，删除某个分区对于其他的索引分区没有任何影响

更多 Oracle 相关信息见 Oracle 专题页面 http://www.linuxidc.com/topicnews.aspx?tid=12

本文永久更新链接地址 ：http://www.linuxidc.com/Linux/2017-10/147215.htm

正文完

星哥说事-微信公众号