全局索引:
create index idx_t1 on system.t1(date_id,comp_kpi_code) tablespace users parallel 4;
--实际上,不加global关键字创建的也是全局索引
--680G的分区表,16并行创建索引,大约用时90分钟左右
alter index system.idx_t1 noparallel;
--为了建索引速度快,会加上并行,加上并行之后,此索引就会是并行了。
--访问有并行度的索引时,CBO可能可能会考虑并行执行,这可能会引发一些问题,如在服务器资源紧张的时候用并行会引起更加严重的争用。当使用并行后,需要把索引的并行度改成1
SQL> select owner,index_name,degree,partitioned,status,last_analyzed from dba_indexes where table_name='T1';
OWNER INDEX_NAME DEGREE PARTITIONED STATUS LAST_ANALYZED
SYSTEM IDX_T1 1 NO VALID 2018/8/2 8:56
move分区,truncate分区,rename表,drop分区后对索引的影响测试:
alter table system.t1 move partition PART201806 tablespace TBS_ZBA_DMA;
--move的分区如果没有数据,索引不会失效否则会失效
alter table system.t1 move partition PART201808 tablespace users update indexes parallel 4;
--move表空间的同时自动维护索引,并且索引的degree不会改变
alter table system.t1 truncate partition part201806;
--如果分区上有数据索引会失效,否则不会失效
alter table system.t1 truncate partition part201806 update indexes;
--truncate分区并维护索引,防止索引失效
alter table system.t1 drop partition PART201809;
alter table system.t1 rename to t2;
--分区表上的索不失效
alter index system.idx_t1 unusable;
alter table system.t1 move partition PART201808 tablespace users update indexes parallel 4;
--上面两种,都会更新dba_objects.last_ddl_time时间
select * from DBA_PART_KEY_COLUMNS where name='T1';
--查看某个分区表的分区键
create index system.idx_t1_global on system.t1(month_id) global
partition by range(date_id,comp_kpi_code)
( partition part2017 values less than ('201801') tablespace users,
partition part2018 values less than ('201901') tablespace users,
partition partmax values less than (maxvalue) tablespace users
);
--全局分区索引,只能创建在分区键上。
move有数据的分区表后,全局索引失效,get_ddl中就会有
alter index xxxx unusable的语句。
last_ddl_time的时间不会更新。 本地索引:
create index system.idx_t1_local on system.t1(date_id,comp_kpi_code) local tablespace users parallel 2;
SQL> select owner,index_name,degree,partitioned,status,last_analyzed from dba_indexes where table_name='T1' and owner='SYSTEM';
OWNER INDEX_NAME DEGREE PARTITIONED STATUS LAST_ANALYZED
SYSTEM IDX_T1_LOCAL 1 YES N/A 2018/8/2 8:11
select owner,index_name,degree,partitioned,status,last_analyzed from dba_indexes where table_name='T1';
--如果是分区索引,partitioned=yes,status=N/A
select p.owner,p.index_name,p.alignment,i.partition_name,i.status,i.tablespace_name,i.logging,i.compression,i.last_analyzed
from dba_part_indexes p,dba_ind_partitions i
where p.index_name=i.index_name and p.owner=i.index_owner
and p.table_name='T1';
--查看分区索引的信息
alter table system.t1 move partition PART201806 tablespace TBS_ZBA_DMA;
--本地索引会变为unusable
alter index system.idx_t1_local rebuild partition part201806 online parallel 4;
alter index system.idx_t1_local noparallel;
--重建本地分区索引
alter table t1 truncate partition part201808;
--不影响本地分区索引
alter table t1 add partition part201809 values less than ('201810') ;
--添加分区会自动添加分区索引
alter table system.t1 drop partition PART201809;
--不影响其他分区上的索引
alter table system.t1 rename to t2;
--不影响分区索引状态
测试用表:
CREATE TABLE "SYSTEM"."T1"
( "MONTH_ID" VARCHAR2(6),
"DAY_ID" VARCHAR2(2),
"DATE_ID" VARCHAR2(8),
"PROV_ID" VARCHAR2(10),
"AREA_NO" VARCHAR2(10),
"PRODUCT_CLASS" VARCHAR2(30),
"COMP_KPI_CODE" VARCHAR2(50),
"KPI_VALUE" NUMBER,
"D_LD_VA