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

千家信息网最后更新 2025年01月22日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,
name VARCHAR2(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,
name VARCHAR2(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'));