分区表按照类型可以分为范围分区(Range)、列表分区(List)以及哈希分区(Hash),表被分区后,其对应的索引也会与普通表的索引有所不同。
基本概念
对于分区表上的索引可以分为两类:本地索引和全局索引。其中全局索引又可以分为分区索引和未分区索引,而本地索引必须为分区索引。因此若某个索引未分区,可以直接断定其为全局索引;若已分区,在根据具体情况进行判断。官方截图如下:
本文重点说明Local Partitioned Index(为配合上图此处才这么写,可以简写为Local Index,因为Local Index一定是分区的)和Global Partitioned Index的基本概念原理以及其对应使用的场景。
本地索引的分区机制与表的分区机制一样:其分区键与分区表的分区键相同,同样分区个数与基表分区个数也相同,因此本地索引分区情况与基表一模一样。
全局分区索引分区键以及分区个数与基表可能不同,另外全局分区索引必须为前缀索引,即其索引分区键必须为索引的前几列。
用文字描述比较枯燥,难于理解,下图比较形象的说明了两者以及两者的区别。
Local Index global Partition Index
应用场景
那么本地索引和全局分区索引或全局索引究竟适用于什么场景呢?,下面以本地索引和全局索引(非分区)为例进行说明:
1. 新建测试以ID为分区键的范围分区表T_PARTITION,具体表结构如下:
desc t_partition;
Name Null? Type
----------------------------------------- -------- ----------------------------
ID NUMBER
DATA VARCHAR2(100)
T_PARTITION以列ID间隔100分别创建了10个分区,如下
-
SQL> SELECT count(*) FROM user_tab_partitions WHERE table_name='T_PARTITION';
COUNT(*)
----------
1000
T_PARTITION的数据量情况如下:
-
SQL> SELECT count(*) FROM t_partition;
-
-
COUNT(*)
-
----------
-
11030039
2.在DATA列分别创建一个本地索引一个全局索引。
本地索引:
-
create index ind_l_data on t_partition(data) local;
-
Index created.
创建完成后,通过以下语句检查索引的具体情况,通过输出可以看到:如同前面说的一样本地索引与基表分区情况一样。
-
SELECT table_name,locality,alignment FROM user_part_indexes WHERE index_name='IND_L_DATA';
-
TABLE_NAME LOCALITY ALIGNMENT
-
---------------- ----------- ------------
-
T_PARTITION LOCAL NON_PREFIXED
-
-
SELECT count(*) FROM user_ind_partitions t WHERE index_name='IND_L_DATA';
COUNT(*)
----------
1000
全局分区索引:
-
drop index ind_l_data;
-
create index ind_g_data on test_partition(data);
-
Index created.
3.比较本地索引以及全局索引
分为两种情况比较,一种情况查询谓词中带有基表的分区键,另外一种不带基表分区键,先看条件不带分区键的情况:
本地索引:
全局索引:
通过上面两个图输出的统计信息,可以看到逻辑读(consistent gets)全局索引
明显要比本地索引逻辑读少,全局索引的效率更高。
下面再观察一下谓词中带有分区键的情况:
本地索引:
全局索引:
相差不大,相对来说本地索引性能比全局索引性能更好一下。
全局索引和本地索引各自有各自的特点,使用场景不同,DBA应根据具体的业务情况创建相应的索引,另外从Oracle官方可以找到关于本地索引和全局索引的一点点建议:
由此推断:全局索引多用于OLTP系统,而本地索引都用于OLAP系统。
维护注意事项
1.本地索引可以为前缀索引和非前缀索引,全局索引必须为前缀索引。如下我们全局索引创建在data列,通过id列分区,系统报错GLOBAL partitioned index must be prefixed.
2.只要查询条件中包含了基表的分区键,非前缀、前缀索引都支持分区消除。
3.本地索引由Oracle自动管理,当有添加、删除、合并分区、截断分区数据等操作,本地索引自动更新,但全局索引不会自动更新,一旦有上述操作请记得
添加update global indexes语句,若不添加索引就会变得不可用。
本地索引:
全局索引:
可以看到若不添加update global indexes,全局索引状态会变为不可用,因此在
日常维护全局索引过程中请注意此问题。