oracle表分区失效14400,Oracle分区表全局索引添加分区时不会失效

COALESCE PARTITION | SUBPARTITION (hash)

DROP PARTITION | SUBPARTITION

EXCHANGE PARTITION | SUBPARTITION

MERGE PARTITION | SUBPARTITION

MOVE PARTITION | SUBPARTITION

SPLIT PARTITION | SUBPARTITION

TRUNCATE PARTITION | SUBPARTITION

上面所有操作都会引起global索引失效。

其中下面的操作会引起local索引失效。

MERGE PARTITION

MOVE PARTITION

SPLIT PARTITION

COALESCE PARTITION

EXCHANGE PARTITION

此处测试间隔分区和时间分区，增加分区global和local分区均不会失效，删除分区导致global索引失效

创建测试表

create table FACT_MONTH1

(

stat_date   DATE,

nameVARCHAR2(6)

)

PARTITION BY RANGE (stat_date)

INTERVAL (NUMTOYMINTERVAL(1,'MONTH'))

(

PARTITION P1 VALUES LESS THAN (TO_DATE('2014-1-1', 'YYYY-MM-DD'))

);

插入测试数据

insert into FACT_MONTH1TIME values(TO_DATE('2013-11-1', 'YYYY-MM-DD') ,'ABCDE');

insert into FACT_MONTH1TIME values(TO_DATE('2014-1-1', 'YYYY-MM-DD') ,'ABCDE');

insert into FACT_MONTH1TIME values(TO_DATE('2014-1-15', 'YYYY-MM-DD'),'ABCDE');

insert into FACT_MONTH1TIME values(TO_DATE('2014-2-15', 'YYYY-MM-DD'),'ABCDE');

insert into FACT_MONTH1TIME values(TO_DATE('2014-3-15', 'YYYY-MM-DD'),'ABCDE');

insert into FACT_MONTH1TIME values(TO_DATE('2014-4-15', 'YYYY-MM-DD'),'ABCDE');

insert into FACT_MONTH1TIME values(TO_DATE('2014-5-15', 'YYYY-MM-DD'),'ABCDE');

insert into FACT_MONTH1TIME values(TO_DATE('2014-6-15', 'YYYY-MM-DD'),'ABCDE');

insert into FACT_MONTH1TIME values(TO_DATE('2014-7-15', 'YYYY-MM-DD'),'ABCDE');

commit;

查看各种信息

select * from FACT_MONTH1;

select * from user_tab_partitions;

select * from user_part_tables;

select * from user_ind_partitions;

select * from user_part_indexes;

创建索引

create index fa_idx_g on FACT_MONTH1(stat_date);

create index fa_idx_local on FACT_MONTH1(name) local;

查看索引信息

select * from user_tab_partitions;

select * from user_part_tables;

select * from user_ind_partitions;

select * from user_part_indexes;

select * from user_indexes;

添加分区:

insert into FACT_MONTH1 values(TO_DATE('2014-9-15', 'YYYY-MM-DD'),'ABCDE');

commit;

查看索引是否可用:

alter session set statistics_level=all ;

select /*+ index(FA_IDX_G)*/ * from FACT_MONTH1 where stat_date=TO_DATE('2014-9-15', 'YYYY-MM-DD');

select * from table(dbms_xplan.display_cursor(null,null,'allstats last'));

时间分区测试

CREATE TABLE FACT_MONTH1TIME

(

stat_date   DATE,

nameVARCHAR2(6)

)

PARTITION BY RANGE (stat_date)

(

PARTITION p1 VALUES LESS THAN (TO_DATE('2014-1-1', 'yyyy-mm-dd')) ,

PARTITION p2 VALUES LESS THAN (TO_DATE('2014-2-1', 'yyyy-mm-dd')) ,

PARTITION p3 VALUES LESS THAN (TO_DATE('2014-3-1', 'yyyy-mm-dd')) ,

partition p4 values less than (maxvalue)

);

插入测试数据

insert into FACT_MONTH1TIME values(TO_DATE('2013-11-1', 'YYYY-MM-DD') ,'ABCDE');

insert into FACT_MONTH1TIME values(TO_DATE('2014-1-1', 'YYYY-MM-DD') ,'ABCDE');

insert into FACT_MONTH1TIME values(TO_DATE('2014-1-15', 'YYYY-MM-DD'),'ABCDE');

insert into FACT_MONTH1TIME values(TO_DATE('2014-2-15', 'YYYY-MM-DD'),'ABCDE');

insert into FACT_MONTH1TIME values(TO_DATE('2014-3-15', 'YYYY-MM-DD'),'ABCDE');

insert into FACT_MONTH1TIME values(TO_DATE('2014-4-15', 'YYYY-MM-DD'),'ABCDE');

insert into FACT_MONTH1TIME values(TO_DATE('2014-5-15', 'YYYY-MM-DD'),'ABCDE');

insert into FACT_MONTH1TIME values(TO_DATE('2014-6-15', 'YYYY-MM-DD'),'ABCDE');

insert into FACT_MONTH1TIME values(TO_DATE('2014-7-31', 'YYYY-MM-DD'),'ABCDE');

commit;

创建索引

create index fa_idxtime_g on FACT_MONTH1TIME(stat_date);

create index fa_idxtime_local on FACT_MONTH1TIME(name) local;

增加分区

alter table FACT_MONTH1TIME add partition p6 VALUES LESS THAN (TO_DATE('2014-6-1', 'yyyy-mm-dd'));

alter table FACT_MONTH1TIME add partition p8 VALUES LESS THAN (TO_DATE('2014-8-1', 'yyyy-mm-dd'));

alter table FACT_MONTH1TIME drop partition p8;

查看索引是否可用

alter session set statistics_level=all ;

select /*+ index(FA_IDXTIME_G)*/ * from FACT_MONTH1TIME where stat_date=TO_DATE('2014-9-15', 'YYYY-MM-DD');

select * from table(dbms_xplan.display_cursor(null,null,'allstats last'));

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