真实的国产乱ⅩXXX66竹夫人,五月香六月婷婷激情综合,亚洲日本VA一区二区三区,亚洲精品一区二区三区麻豆

成都創(chuàng)新互聯(lián)網(wǎng)站制作重慶分公司

Oracle分區(qū)表全局索引添加分區(qū)時(shí)不會(huì)失效

COALESCE PARTITION | SUBPARTITION (hash)
DROP PARTITION | SUBPARTITION
EXCHANGE PARTITION | SUBPARTITION
MERGE PARTITION | SUBPARTITION
MOVE PARTITION | SUBPARTITION
SPLIT PARTITION | SUBPARTITION
TRUNCATE PARTITION | SUBPARTITION
上面所有操作都會(huì)引起global索引失效。
其中下面的操作會(huì)引起local索引失效。
MERGE PARTITION
MOVE PARTITION
SPLIT PARTITION
COALESCE PARTITION
EXCHANGE PARTITION

此處測(cè)試間隔分區(qū)和時(shí)間分區(qū),增加分區(qū)global和local分區(qū)均不會(huì)失效,刪除分區(qū)導(dǎo)致global索引失效
創(chuàng)建測(cè)試表
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'))
  );
插入測(cè)試數(shù)據(jù)
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;
創(chuàng)建索引
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;
添加分區(qū):
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'));




時(shí)間分區(qū)測(cè)試
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)
);
插入測(cè)試數(shù)據(jù)
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;
創(chuàng)建索引
create index fa_idxtime_g on FACT_MONTH1TIME(stat_date);
create index fa_idxtime_local on FACT_MONTH1TIME(name) local;
增加分區(qū)
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'));

網(wǎng)站標(biāo)題:Oracle分區(qū)表全局索引添加分區(qū)時(shí)不會(huì)失效
文章源于:http://weahome.cn/article/pdchph.html

其他資訊

在線咨詢

微信咨詢

電話咨詢

028-86922220(工作日)

18980820575(7×24)

提交需求

返回頂部