【SQL开发实战技巧】系列（四十九）：Oracle12C常用新特性?表分区部分索引(Partial Indexes)

2023-12-13 23:17:55

系列文章目录

文章目录

系列文章目录
前言
【SQL开发实战技巧】系列（四十七）：Oracle12C常用新特性?表分区部分索引(Partial Indexes)
案例一：比较partial index与global/local index
案例二：执行计划

前言

本篇文章主要给讲解的Oracle12C+的新特性是：Partial Indexes(部分索引), 无论是global还是local都可以有选择性的对部分分区创建索引，并通过比较partial index与global/local index两个案例来看对查询效率的提升！

【SQL开发实战技巧】这一系列博主当作复习旧知识来进行写作，毕竟SQL开发在数据分析场景非常重要且基础，面试也会经常问SQL开发和调优经验，相信当我写完这一系列文章，也能再有所收获，未来面对SQL面试也能游刃有余~。

【SQL开发实战技巧】系列（四十七）：Oracle12C常用新特性?表分区部分索引(Partial Indexes)

12c之前没办法在部分或指定的分区上创建索引,12c 版本中引入了Partial Indexes(部分索引), 无论是global还是local都可以有选择性的对部分分区创建索引。分区上有索引用索引，无索引全表扫，跨多分区中indexing有开有关时有用索引和全表的子集union all。
partial index有两个限制条件:
1、表是分区表
2、不能是唯一索引
3、建表的时候分区指定 INDEXING OFF or ON 或者 alter table 修改

案例一：比较partial index与global/local index

创建测试用表：

CREATE TABLE part_ind
(ID number(10), NAME varchar2(30))
INDEXING OFF
PARTITION BY RANGE (id)
(PARTITION p1 VALUES LESS THAN (200) INDEXING OFF,
PARTITION p2 VALUES LESS THAN (400),
PARTITION p3 VALUES LESS THAN (600),
PARTITION p4 VALUES LESS THAN (800) INDEXING ON,
partition p5 values less than (maxvalue)
);

插入数据：

begin
for i in 1 .. 1000 loop
insert into part_ind values(i,'part_ind');
end loop ;
commit;
end;
/

查看现在表分区：

select table_name,partition_name,
PARTITION_POSITION,
tablespace_name,
HIGH_VALUE,
indexing 
from user_tab_partitions 
where table_name='PART_IND';

在这里插入图片描述

Create table属性indexing的默认值是ON;
partition 或sub partition如果没有指定indexing是继承上级属性。
创建global索引

create index idx_p_id on PART_IND(id);

查看索引状态

select table_name,index_name,partitioned,blevel,leaf_blocks,indexing from user_Indexes where index_name ='IDX_P_ID';

在这里插入图片描述

drop index IDX_P_ID;

create index IDX_P_ID on part_ind(id) GLOBAL INDEXING FULL;
select 
table_name, 
index_name, 
partitioned, 
blevel, 
leaf_blocks,
 indexing
  from user_Indexes
 where index_name = 'IDX_P_ID';

在这里插入图片描述

drop index IDX_P_ID;
create index IDX_P_ID on part_ind(id) GLOBAL INDEXING PARTIAL;
select table_name,
 index_name,
 partitioned,
 blevel, 
leaf_blocks, 
indexing
  from user_Indexes
 where index_name = 'IDX_P_ID';

在这里插入图片描述

Create Index 引入了INDEXING ON|OFF|PARTIAL|FULL值, INDEXING FULL是默认值. 上面创建了global index full和index partial 可以看到部分索引的index leaf block和number of rows都明显减少。
接下来看下本地索引和部分本地索引得区别：

drop index idx_p_id;

创建LOCAL索引

create index idx_p_id_loc on part_ind(id) local;

查看索引状态

select table_name,index_name,partitioned,blevel,leaf_blocks,indexing from user_Indexes where index_name ='IDX_P_ID_LOC';

在这里插入图片描述

drop index idx_p_id_loc;

创建LOCAL索引

create index idx_p_id_loc on part_ind(id) local INDEXING PARTIAL;

查看索引状态

select table_name, index_name, partitioned, blevel, leaf_blocks, indexing
  from user_Indexes
 where index_name = 'IDX_P_ID_LOC';

在这里插入图片描述

创建了local index full和index partial 可以看到部分索引的index leaf blocks和number of rows都明显减少.

案例二：执行计划

查看表索引

SELECT index_name, column_name, descend
  FROM user_ind_columns
 WHERE table_name = 'PART_IND';

在这里插入图片描述

删除所有索引，为了方便下面创建部分索引。

drop index IDX_P_ID_LOC

查看现在表的分区：

select table_name,
       partition_name,
       PARTITION_POSITION,
       tablespace_name,
       HIGH_VALUE,
       indexing
  from user_tab_partitions
 where table_name = 'PART_IND';

在这里插入图片描述

创建全局索引：

create index idx_p_id_G on PART_IND(id) GLOBAL INDEXING PARTIAL;

验证是不是部分全局索引：

select index_name,indexing from dba_indexes where index_name='IDX_P_ID_G';

在这里插入图片描述
–是

select table_name, index_name, status, blevel, leaf_blocks
  from user_Indexes
 where index_name = 'IDX_P_ID_G';

在这里插入图片描述

改变分区 indexing 属性：

alter table PART_IND modify partition P3 indexing off;
alter table PART_IND modify partition P4 indexing on;
select table_name, index_name, status, blevel, leaf_blocks
  from user_Indexes
 where index_name = 'IDX_P_ID_G';

在这里插入图片描述

select table_name,
       partition_name,
       PARTITION_POSITION,
       tablespace_name,
       HIGH_VALUE,
       indexing
  from user_tab_partitions
 where table_name = 'PART_IND';

在这里插入图片描述

改变分区 indexing 属性
创建local 索引：

create index idx_p_id_L on PART_IND(id) local INDEXING PARTIAL;
ORA-01408: such column list already indexed
create index idx_p_id_L on PART_IND(id) local INDEXING PARTIAL invisible;

同一字段上不能创建两个相同种类的索引，这里把第二次创建的索引设置为 invisible 。

select index_name, partition_name, STATUS
  from user_ind_partitions
 where index_name = 'IDX_P_ID_L';

在这里插入图片描述

local index 标记为INDEXING OFF的分区索引状态将显示为UNUSABLE，标记为INDEXING ON的分区索引状态显示为USABLE。
看下sql执行时执行计划：

select * from PART_IND where id>550;

在这里插入图片描述

select * from PART_IND where id=550

在这里插入图片描述

alter index IDX_P_ID_G invisible;
alter index IDX_P_ID_L visible;
select * from PART_IND where id=770;

在这里插入图片描述

select * from PART_IND where id>770;

在这里插入图片描述

分区上有索引用索引（也可能存在某种情况不走索引），无索引全表扫，跨多分区中indexing有开有关时有用索引和全表的子集union all。

文章来源:https://blog.csdn.net/qq_28356739/article/details/134856451
本文来自互联网用户投稿，该文观点仅代表作者本人，不代表本站立场。本站仅提供信息存储空间服务，不拥有所有权，不承担相关法律责任。如若内容造成侵权/违法违规/事实不符，请联系我的编程经验分享网邮箱：veading@qq.com进行投诉反馈，一经查实，立即删除！