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

官网截图:

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

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

相关推荐