Oracle 知识篇+分区表上的索引由global改为local注意事项

这篇具有很好参考价值的文章主要介绍了Oracle 知识篇+分区表上的索引由global改为local注意事项。希望对大家有所帮助。如果存在错误或未考虑完全的地方,请大家不吝赐教,您也可以点击"举报违法"按钮提交疑问。

★ 知识点

二、知识点
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_name
  from dba_part_indexes a
 where a.owner = 'USER_NAME' --用户名
   and a.table_name = 'TABLE_NAME' --表名
   and a.locality = 'GLOBAL'
union
select b.owner, b.table_name, b.index_name
  from dba_indexes b
 where 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.status
  from dba_ind_subpartitions s where s.index_name = 'INDEX_NAME' --索引名
union all
select p.index_owner, p.index_name, p.partition_name, 'NULL', p.status
  from dba_ind_partitions p where p.index_name = 'INDEX_NAME' --索引名
union all
select i.owner, i.index_name, 'NULL', 'NULL', i.status
  from 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.status
  from 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.status
  from dba_ind_partitions p where p.index_name = 'P_GLOBAL_NOTPARTITION'
union all
select i.owner, i.index_name, 'NULL', 'NULL', i.status
  from 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.status
  from 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.status
  from dba_ind_partitions p where p.index_name = 'P_GLOBAL_NOTPARTITION'
union all
select i.owner, i.index_name, 'NULL', 'NULL', i.status
  from 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.status
  from 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.status
  from dba_ind_partitions p where p.index_name = 'P_GLOBAL_NOTPARTITION'
union all
select i.owner, i.index_name, 'NULL', 'NULL', i.status
  from 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.status
  from 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.status
  from dba_ind_partitions p where p.index_name = 'P_GLOBAL_PARTITION'
union all
select i.owner, i.index_name, 'NULL', 'NULL', i.status
  from 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.status
  from 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.status
  from dba_ind_partitions p where p.index_name = 'P_GLOBAL_PARTITION'
union all
select i.owner, i.index_name, 'NULL', 'NULL', i.status
  from 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.status
  from 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.status
  from dba_ind_partitions p where p.index_name = 'P_GLOBAL_PARTITION'
union all
select i.owner, i.index_name, 'NULL', 'NULL', i.status
  from 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.status
  from 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.status
  from dba_ind_partitions p where p.index_name = 'P_LOCAL_PARTITION'
union all
select i.owner, i.index_name, 'NULL', 'NULL', i.status
  from 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.status
  from 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.status
  from dba_ind_partitions p where p.index_name = 'P_LOCAL_PARTITION'
union all
select i.owner, i.index_name, 'NULL', 'NULL', i.status
  from dba_indexes i where i.index_name = 'P_LOCAL_PARTITION'
order by 1, 2, 3,4;
(6)小结
分区表中分区的drop/truncate操作不会造成local型(分区)索引整体变为不可用状态
5.

※ 如果您觉得文章写的还不错, 别忘了在文末给作者点个赞哦 ~

Oracle 知识篇+分区表上的索引由global改为local注意事项,数据库+Oracle,oracle,数据库,分区表,分区索引,分区索引类型修改文章来源地址https://www.toymoban.com/news/detail-647888.html

到了这里,关于Oracle 知识篇+分区表上的索引由global改为local注意事项的文章就介绍完了。如果您还想了解更多内容,请在右上角搜索TOY模板网以前的文章或继续浏览下面的相关文章,希望大家以后多多支持TOY模板网!

原文地址:https://blog.csdn.net/zzt_2009/article/details/132223792

本文来自互联网用户投稿,该文观点仅代表作者本人,不代表本站立场。本站仅提供信息存储空间服务,不拥有所有权,不承担相关法律责任。如若转载,请注明出处: 如若内容造成侵权/违法违规/事实不符,请点击违法举报进行投诉反馈,一经查实,立即删除!

领支付宝红包 赞助服务器费用

相关文章

觉得文章有用就打赏一下文章作者

支付宝扫一扫打赏

博客赞助

微信扫一扫打赏

请作者喝杯咖啡吧~博客赞助

支付宝扫一扫领取红包,优惠每天领

二维码1

领取红包

二维码2

领红包