全局索引分区与局部索引分区演示

Oracle 12c

 

-- 创建普通表

create table test.t_test1 (

    c1 number not null,

    c2 number not null,

    c3 number not null

);

 

-- 局部索引，报错：ORA-14016: 必须对 LOCAL 分区索引的基础表进行分区

drop index test.t_test1_idx1;

create index test.t_test1_idx1 on test.t_test1 (c1) local;

 

-- 全局索引+未分区，无意义；相当于普通索引

drop index test.t_test1_idx2;  

create index test.t_test1_idx2 on test.t_test1 (c1) global;

 

-- 全局索引+已分区，索引列 等于 索引分区列 或者 满足从左匹配 包含索引分区列

drop index test.t_test1_idx3; 

create index test.t_test1_idx3 on test.t_test1 (c1,c2) global

partition by hash (c1);

 

-- 全局索引+已分区，索引列 既不等于 索引分区列 也不满足 从左匹配 包含索引分区列，报错：ORA-14038: GLOBAL 分区索引必须加上前缀

drop index test.t_test1_idx4;

create index test.t_test1_idx4 on test.t_test1 (c1) global

partition by hash(c2);

 

总结：针对没有分区的表来说，无法使用局部索引，可以使用全局(分区/未分区)索引，但是意义不大；

通常是数据容量、或者说索引容量很大时，对其进行分区，提升性能；既然表未分区，说明未达到性能瓶颈，可以不作索引分区；所以下述重点演示 局部索引分区，全局索引分区

 

-- 分区表

create table test.t_test2 (

    c1 number not null,

    c2 number not null,

    c3 number not null

) partition by hash (c1) partitions 3;

 

-- 局部索引+前缀，索引列 等于 分区列，

drop index test.t_test1_idx1;

create index test.t_test1_idx1 on test.t_test2 (c2, c1) local;

 

-- 局部索引+非前缀，索引列 不等于 分区列

drop index test.t_test1_idx2;

create index test.t_test1_idx2 on test.t_test2 (c2) local;

 

注：局部索引的分区方式 和 主表的分区方式是一样的，所以局部索引的分区方式：HASH，分区列：c1，分区数：3 这是一个隐含的条件；

  

-- 全局索引+未分区，无意义；相当于普通索引

drop index test.t_test1_idx3;  

create index test.t_test1_idx3 on test.t_test2 (c1) global;

 

-- 全局索引+已分区+前缀，索引列必须（等同于索引分区列，或者 满足左匹配索引分区列 ）

drop index test.t_test1_idx5; 

create index test.t_test1_idx5 on test.t_test2 (c2,c1) global

partition by hash (c2);

 

-- 全局索引+已分区+非前缀，报错：ORA-14038: GLOBAL 分区索引必须加上前缀

drop index test.t_test1_idx5;

create index test.t_test1_idx5 on test.t_test2 (c1) global

partition by hash(c2);

 

总结：局部索引可以支持前缀，或者非前缀；全局索引必须是前缀；

 

值得思考的问题：

1. 如何理解局部索引可以是 前缀 或 非前缀，全局索引必须是前缀？

2. 如何理解局部分区索引的前缀 & 非前缀的本质区别？

3. 如何理解局部索引 & 全局索引的实际应用场景？

4. 如何理解局部分区索引 & 全局分区索引唯一性？

5. 如何理解局部分区索引 & 全局分区索引的管理？

 

如何查看索引分区类型，是否前缀等信息？

 

select index_name,partitioning_type,subpartitioning_type,locality,alignment from all_part_indexes where owner=?;

 

About Partition Pruning: 

http://www.dba-oracle.com/t_partition_pruning.htm

https://dev.mysql.com/doc/refman/8.0/en/partitioning-pruning.html

 

注意：优化器是否启用 Partition Pruning 要结合查询条件来看，网上不少资料介绍说：Local Prefixed 启用 Partition Pruning 优化，而 Non-Prefixed 则不会启用这项优化；这里可能有误解

WHERE 包含：   完整的分区列，无论是 Prefixed 或 Non-Prefixed 优化器都会启用 Partition Pruning 优化

WHERE 不包含：完整的分区列，优化器都不会启用 Partition Pruning 优化