★ 知識點

二、知識點
?Local型索引有如下優點
1.Only one index partition must be rebuilt when a maintenance operation other than SPLIT PARTITION or ADD PARTITION is performed on an underlying table partition.
2.The duration of a partition maintenance operation remains proportional to partition size if the partitioned table has only local indexes.
3.Local indexes support partition independence.
4.Local indexes support smooth roll-out of old data and roll-in of new data in historical tables.
5.Oracle can take advantage of the fact that a local index is equipartitioned with the underlying table to generate better query access plans.
6.Local indexes simplify the task of tablespace incomplete recovery. To recover a partition or subpartition of a table to a point in time, you must also recover the corresponding index entries to the same point in time. The only way to accomplish this is with a local index. Then you can recover the corresponding table and index partitions or subpartitions.
?對local型索引的分區表執行truncate/drop操作會級聯維護相關分區上的索引并釋放其空間
?改造后對單獨分區的truncate/drop等部分操作不會影響其他分區
?改造后相關表上分區數據的維護/操作會更加方便快捷，如整理分區碎片/清空分區數據/刪除分區數據
?重建索引可以消除索引級的碎片

★ 注意事項

1.分區表中分區的drop/truncate操作會造成global型（非分區）索引變為不可用狀態，但可通過重建索引恢復
2.分區表中分區的drop/truncate操作會造成global型（分區）索引變為不可用狀態，但可通過重建索引恢復
3.分區表中分區的drop/truncate操作不會造成local型（分區）索引變為不可用狀態
4.Oracle要求分區表上的local型唯一/主鍵約束索引必須包含分區鍵，且分區鍵上不容許使用函數
5.唯一/主鍵約束索引由global改local型須添加分區鍵，但這會改變其唯一邏輯，因此需要考慮是否滿足業務。推薦從從業務邏輯層控制數據唯一性，或對該表唯一性無要求的情況下再進行global改local操作
global型單列主鍵		local型組合主鍵		對比說明
id(int)		sj(date)	     id(int)	local型索引須包含分區鍵（本例為sj列date類型）
1		  2023-01-01 00:00:01	1		global型主鍵id列全表只能有一個1
2		  2023-01-01 00:00:02	1		local型主鍵id列全表可以有多個1
3		  2023-01-01 00:00:02	2		local型主鍵的唯一性體現在組合的最小粒度上
6.若表中數據從來源可保證數據唯一性或對唯一性無要求時，推薦用常規索引代替唯一/主鍵約束索引
7.唯一/主鍵約束索引的狀態為UNUSABLE/INVALID等異常狀態時，一般會阻止表上數據的DML，索引重建后恢復
8.索引的修改屬于DDL操作，會對表加不同粒度的鎖，推薦在非業務期間執行，如果是7*24的業務需要酌情使用適當的方式
9.索引狀態列status有3類值：可用(USABLE/VALID)、不可用(UNUSABLE/INVALID)、未知(N/A)
10.索引相關視圖：dba_ind_subpartitions（子分區信息）、dba_ind_partitions（分區信息）、dba_indexes（總信息）
11.Oracle為local型索引創建與分區表相同數量的分區或子分區，并為它們提供與表相應分區相同的分區范圍
12.當基礎表中的分區added, dropped, merged, or split時，或hash partitions or subpartitions被添加或合并時，Oracle 還會自動維護索引分區
13.如果在分區和索引級別沒有用戶指定的TABLESPACE值，則使用基礎表的相應分區的物理屬性的值。
14.唯一約束索引升級為主鍵約束時，Oracle會給相關列設置not null約束，如果相關列不全有not null約束，Oracle會給該表加獨占鎖且鎖定時長和數據量成正比，鎖持續期間表無法增刪改查
15.重建索引時可以酌情考慮開啟并行加快重建速度，重建完成后須關閉并行
16.重建索引時可以酌情考慮是否使用online方式（適合業務繁忙的場景，速度慢但對表的鎖影響教小：僅DDL始末加一小會鎖），非online方式（適合業務不忙的場景，速度快但對表的鎖影響較大：重建索引期間一直加獨占鎖）
17.所有改造操作強烈建議進行充分評估和測試，減少投產風險
18.創建/重建索引會消耗歸檔空間，建議提前排查歸檔空間是否足夠（建議預留超過新索引大小的歸檔空間，該值為預估值，實際可能有出入，建議預留充足的空間）
19.當基礎表分區中的數據被移動或刪除（SPLIT、MOVE、DROP或TRUNCATE）時，全局索引的所有分區都會受到影響。因此，全局索引不支持分區獨立性。
20.分區表上的普通索引由global改為local型時，需要先刪除舊global索引然后新建local索引，期間表上會暫時缺少相關列的索引，如果表上有業務運行可能會因為索引缺失影響性能，建議在非相關業務期間刪除+新建。或在相關列上先建立一個臨時多列組合索引，再刪除舊global索引，然后新建local索引，最后刪除臨時索引
21.

★ 相關SQL

1.查看分區表上的global型索引
select a.owner, a.table_name, a.index_namefrom dba_part_indexes awhere a.owner = 'USER_NAME' --用戶名and a.table_name = 'TABLE_NAME' --表名and a.locality = 'GLOBAL'
union
select b.owner, b.table_name, b.index_namefrom dba_indexes bwhere b.owner = 'USER_NAME'  --用戶名and b.table_name = 'TABLE_NAME' --表名and b.partitioned = 'NO';
2.查看索引狀態（status列USABLE/VALID代表索引可用/正常）
select s.index_owner,s.index_name,s.partition_name,s.subpartition_name,s.statusfrom dba_ind_subpartitions s where s.index_name = 'INDEX_NAME' --索引名
union all
select p.index_owner, p.index_name, p.partition_name, 'NULL', p.statusfrom dba_ind_partitions p where p.index_name = 'INDEX_NAME' --索引名
union all
select i.owner, i.index_name, 'NULL', 'NULL', i.statusfrom dba_indexes i where i.index_name = 'INDEX_NAME' --索引名
order by 1, 2, 3,4;

★ 測試案例

1.創建/重置測試表和數據
--刪表
drop table P;
--建表
create table p (id number,name varchar2(20)) partition by range (id)
(
partition p1 values less than (10) ,
partition p2 values less than (20) ,
partition p3 values less than (30) ,
partition pmax values less than (maxvalue) 
);
--插入實驗數據
insert into p values(1,'p1');
insert into p values(11,'p2');
insert into p values(21,'p3');
insert into p values(91,'pmax');
commit; 
2.分區表上global型（非分區）索引的DDL
（1）測試表重置
（2）創建索引
drop index p_global_notpartition;
create index p_global_notpartition on p(id) global online;
（3）查看索引狀態（status列應該是：VALID可用狀態）
select s.index_owner,s.index_name,s.partition_name,s.subpartition_name,s.statusfrom dba_ind_subpartitions s where s.index_name = 'P_GLOBAL_NOTPARTITION'
union all
select p.index_owner, p.index_name, p.partition_name, 'NULL', p.statusfrom dba_ind_partitions p where p.index_name = 'P_GLOBAL_NOTPARTITION'
union all
select i.owner, i.index_name, 'NULL', 'NULL', i.statusfrom dba_indexes i where i.index_name = 'P_GLOBAL_NOTPARTITION'
order by 1, 2, 3,4;
（4）執行分區DDL操作
alter table p truncate partition p1;
（5）查看索引狀態（status列應該是：UNUSABLE不可用狀態）
select s.index_owner,s.index_name,s.partition_name,s.subpartition_name,s.statusfrom dba_ind_subpartitions s where s.index_name = 'P_GLOBAL_NOTPARTITION'
union all
select p.index_owner, p.index_name, p.partition_name, 'NULL', p.statusfrom dba_ind_partitions p where p.index_name = 'P_GLOBAL_NOTPARTITION'
union all
select i.owner, i.index_name, 'NULL', 'NULL', i.statusfrom dba_indexes i where i.index_name = 'P_GLOBAL_NOTPARTITION'
order by 1, 2, 3,4;
（6）索引重建
alter index p_global_notpartition rebuild online;
（7）查看索引狀態（status列應該是：VALID可用狀態）
select s.index_owner,s.index_name,s.partition_name,s.subpartition_name,s.statusfrom dba_ind_subpartitions s where s.index_name = 'P_GLOBAL_NOTPARTITION'
union all
select p.index_owner, p.index_name, p.partition_name, 'NULL', p.statusfrom dba_ind_partitions p where p.index_name = 'P_GLOBAL_NOTPARTITION'
union all
select i.owner, i.index_name, 'NULL', 'NULL', i.statusfrom dba_indexes i where i.index_name = 'P_GLOBAL_NOTPARTITION'
order by 1, 2, 3,4;
（8）小結
分區表中分區的drop/truncate操作會造成global型（非分區）索引變為不可用狀態
3.分區表上global型（分區）索引的DDL
（1）測試表重置
（2）創建索引
drop index p_global_partition;
create index p_global_partition on p(id) global partition by range (id)
(
partition p1 values less than (10) ,
partition p2 values less than (20) ,
partition p3 values less than (30) ,
partition pmax values less than (maxvalue) 
) online;
（3）查看索引狀態（status列應該是：USABLE可用狀態）
select s.index_owner,s.index_name,s.partition_name,s.subpartition_name,s.statusfrom dba_ind_subpartitions s where s.index_name = 'P_GLOBAL_PARTITION'
union all
select p.index_owner, p.index_name, p.partition_name, 'NULL', p.statusfrom dba_ind_partitions p where p.index_name = 'P_GLOBAL_PARTITION'
union all
select i.owner, i.index_name, 'NULL', 'NULL', i.statusfrom dba_indexes i where i.index_name = 'P_GLOBAL_PARTITION'
order by 1, 2, 3,4;
（4）執行分區DDL操作
alter table p truncate partition p1;
（5）查看索引狀態（status列應該是：UNUSABLE不可用狀態）
select s.index_owner,s.index_name,s.partition_name,s.subpartition_name,s.statusfrom dba_ind_subpartitions s where s.index_name = 'P_GLOBAL_PARTITION'
union all
select p.index_owner, p.index_name, p.partition_name, 'NULL', p.statusfrom dba_ind_partitions p where p.index_name = 'P_GLOBAL_PARTITION'
union all
select i.owner, i.index_name, 'NULL', 'NULL', i.statusfrom dba_indexes i where i.index_name = 'P_GLOBAL_PARTITION'
order by 1, 2, 3,4;
（6）索引重建（分區索引的重建需要在最小粒度上執行，如分區/子分區級）
alter index P_GLOBAL_PARTITION rebuild partition p1 online;
alter index P_GLOBAL_PARTITION rebuild partition p2 online;
alter index P_GLOBAL_PARTITION rebuild partition p3 online;
alter index P_GLOBAL_PARTITION rebuild partition pmax online;
（7）查看索引狀態（status列應該是：USABLE可用狀態）
select s.index_owner,s.index_name,s.partition_name,s.subpartition_name,s.statusfrom dba_ind_subpartitions s where s.index_name = 'P_GLOBAL_PARTITION'
union all
select p.index_owner, p.index_name, p.partition_name, 'NULL', p.statusfrom dba_ind_partitions p where p.index_name = 'P_GLOBAL_PARTITION'
union all
select i.owner, i.index_name, 'NULL', 'NULL', i.statusfrom dba_indexes i where i.index_name = 'P_GLOBAL_PARTITION'
order by 1, 2, 3,4;
（8）小結
分區表中分區的drop/truncate操作會造成global型（分區）索引整體變為不可用狀態
4.分區表上local型（分區）索引的DDL
（1）測試表重置
（2）創建索引
drop index p_local_partition;
create index p_local_partition on p(id) local online;
（3）查看索引狀態（status列應該是：USABLE可用狀態）
select s.index_owner,s.index_name,s.partition_name,s.subpartition_name,s.statusfrom dba_ind_subpartitions s where s.index_name = 'P_LOCAL_PARTITION'
union all
select p.index_owner, p.index_name, p.partition_name, 'NULL', p.statusfrom dba_ind_partitions p where p.index_name = 'P_LOCAL_PARTITION'
union all
select i.owner, i.index_name, 'NULL', 'NULL', i.statusfrom dba_indexes i where i.index_name = 'P_LOCAL_PARTITION'
order by 1, 2, 3,4;
（4）執行分區DDL操作
alter table p truncate partition p1;
（5）查看索引狀態（status列應該是：USABLE可用狀態）
select s.index_owner,s.index_name,s.partition_name,s.subpartition_name,s.statusfrom dba_ind_subpartitions s where s.index_name = 'P_LOCAL_PARTITION'
union all
select p.index_owner, p.index_name, p.partition_name, 'NULL', p.statusfrom dba_ind_partitions p where p.index_name = 'P_LOCAL_PARTITION'
union all
select i.owner, i.index_name, 'NULL', 'NULL', i.statusfrom dba_indexes i where i.index_name = 'P_LOCAL_PARTITION'
order by 1, 2, 3,4;
（6）小結
分區表中分區的drop/truncate操作不會造成local型（分區）索引整體變為不可用狀態
5.

---

※ 如果您覺得文章寫的還不錯，?別忘了在文末給作者點個贊哦 ~