分区表上的索引选择

分区表上的索引可建为Global Index和Local Index两种。

Global Index: Global Index既可以建成分区索引，也可以不分区。分区的方式有range和hash两种，索引分区可能指向表中的任何分区，索引分区列也可以与表分区列不同，索引结构与表是独立的，它更加灵活，因而也需要更多的人工维护操作；

Local Index: Local Index的分区形式与表的分区完全相同，分区所依赖的列也相同，灵活性相对Global Index要差。对于Local Index，其索引分区的维护操作是随着表分区的操作自动进行的，可用性因而较好。

一直对Global Index和Local Index的适用场景认识比较模糊，Tom Kyte给出的经验是“数据仓库系统中大多数分区实现使用的都是Local Index,而在OLTP系统中，全局索引则更为常见”。这里通过试验的方式来帮助自己理解这一问题，并在此作一记录。

OLTP系统的特点是会频繁地出现许多小的读写事务，读取的数据量一般不大，查询时的条件常常会比较灵活。比如，以下测试表：

在OLTP系统中，我们可能根据EMPNO列去作查询，也可能根据JOB或者DEPTNO列作查询，查询的方法可能是多样的。这时, Global Index就显得更合适。

比如，我们要根据EMPNO列的值作以下查询：

在partition_tab表中插入测试数据：

我们在empno列上创建Global索引：

--注意，这里不论是否加global关键字，创建的都是Global Index，因为分区表上创建的索引，要么是Local的，要么是Global的。这个，可以从下面的执行计划 “TABLE ACCESS BY GLOBAL INDEX ROWID”中得到验证。对于普通表上的索引，这里的执行计划为” TABLE ACCESS BY INDEX ROWID”。

Global Index的执行计划如上所示。

如果我们将这里的索引创建为Local Index,结果如何呢？

从这里的执行计划可以看到，当索引创建为Local Index后，为了得到需要的结果，oracle需要探查索引的每个分区，因为这里的索引分区是以loc列分区的，各个分区是独立的数据段，oracle只有深入到每个索引分区才能确定是否包含empno列为7876的记录。

那么，如果上面的Global索引像Local索引一样，也被创建为paritioned,会是什么样呢？

可以看到，这里oracle只用查询Global Index的第4个分区就可以了，因为该Global Index的分区列就是empno, sql在解析时就可以确定需要访问哪个分区。

说到这里，可以看到Global Partitioned Index相对于Local Partitioned Index所体现出来的“灵活性”， 就是由于Global index可以选择不同于底层表的分区方式所带来的，而Local Index只能被动地接受底层表的分区方式。

有一点需要注意，Global Partitioned Index的索引键必须以索引的分区键开始，被称为前缀全局索引(Prefixed Global Index)。

比如，我们可以这么创建索引：

但若像这样创建索引，则会得到ORA-14038错误：

那么，Global Partitioned Index的劣势表现在哪呢？

相对于Local Partitioned Index, Global Partitioned Index的可用性较差。在Local Partitioned Index中，由于索引分区与表分区是一一对应的，关系简单，各个索引分区之间相对独立，当对表的分区作add/drop/merge/split操作时，Local索引是自动维护的，不会由于表上的分区操作而导致索引失效。但对Global Partitioned Index来说，则不存在这种优势，Global Index常常由于表上的分区操作而失效，要知道，这对一个大表来说是很糟糕的，所有使用到该索引的查询语句性能都会受影响。
那么，oracle对于Global Partitioned Index的上述劣势有什么应对措施吗？

答案是”update global indexes”子句。

比如，继续使用上面的测试案例，我们仍在partition_tab表上创建Global Index partition_tab_pk:

这时，如果我们删除表上的一个分区：

可以看到，Global Partitioned Index的所有分区都会失效：

但如果这里建立的是Local Partitioned Index,则情况会不同：

在删除了表上的一个分区后，Local Partitioned Index的对应的索引分区也被删除，但其他索引仍旧能正常工作。这就是为什么说Local Partitioned Index的可用性要更高的原因。

前面我们已经说了，Global Partitioned Index也提供了自己的解决方案”update global indexes”。我们来看看这个子句的使用：

可以看到，在删除表分区时指定update global indexes后，Global Partitioned Index的所有分区仍然有效。

需要指出的时，在添加了update global indexes子句后，表分区维护操作会占用更多的资源，比如需要耗费更多的CPU时间，产生更多的重做日志等，update global indexes方法实际上就是一种通过使用资源来换取整个索引可用型的选择。虽然会耗费更多的资源，但这种选择很多时候是值得的。

再回到前面关于Local Partioned Index的表述，“数据仓库系统中大多数分区实现使用的都是Local Index”，为什么这么说呢？

这是由于，如前所述，Local Partitioned Index的可用性更高，数据仓库系统中分区维护操作较为频繁，如典型的“数据滑动窗口”操作，在这类操作中，Local Partitioned Index更为灵活，如果需要移动表的一个分区，只有相关的索引分区需要重建或维护，而如果是Global Partitioned Index，则所有索引分区都必须重建或者维护（通过update global indexes实现）。

关于索引的分区消除（Partition elimination）

我们知道，对于分区表，分区对数据查询性能的提升主要是通过分区消除来实现的。对于分区索引，如果查询首先fangwe同样可以利用分区消除。

在继续这个讨论之前，先说说Local Partitioned Index的分类，Local Partitioned Index分为Local Prefixed Index和Local Nonprefixed Index两种：

Local Prefixed Index中索引键以索引的分区键作为前导列；

Local Nonprefixed Index中索引键不以索引的分区键作为前导列。所以可能包含分区键，也可能不包含。

继续分区消除的讨论：

创建测试表和测试索引：

这里，我们创建了Local Prefixed Index,Local Nonprefixed Index以及Global Partitioned Index.

接下来，我们看看使用到这三个索引查询语句其执行计划分别是什么样的？

可以看到，通使用索引LOCAL_PREFIXED和GLOCAL_INDEX查询都实现了分区消除，第2个分区不需要考虑。但对于索引LOCAL_NONPREFIXED，则需要考虑所有分区，这是不是说使用Local Nonprefixed Index ”LOCAL_NONPREFIXED”的查询不能实现分区消除呢？

我们删除索引LOCAL_PREFIXED，并重写第二个查询语句：

drop index local_prefixed;

explain plan for select * from partition_tab where a = 1 and b = 1;

select * from table(dbms_xplan.display);

-------------------------------------------------------------------------------

| Id  | Operation                          | Name              |Pstart| Pstop |

-------------------------------------------------------------------------------

|   0 | SELECT STATEMENT                   |                   |      |       |

|   1 |  PARTITION RANGE SINGLE            |                   |    1 |     1 |

|*  2 |   TABLE ACCESS BY LOCAL INDEX ROWID| PARTITION_TAB     |    1 |     1 |

|*  3 |    INDEX RANGE SCAN                | LOCAL_NONPREFIXED |    1 |     1 |

-------------------------------------------------------------------------------

可以看到，新的查询语句使用了所以LOCAL_NONPREFIXED，并且也实现了分区消除。这是因为我们在查询条件中增加了a=1,这个查询条件能帮助我们在语句解析阶段确定需要访问哪个分区。

因此，总结来说，无论是Local Partitioned index还是Global Partitioned Index,无论是Local Prefixed Index还是Local Nonprefixed Index，都是可以利用分区消除的，这取决于你查询语句中的谓词选择，而不是索引类型。

关于索引的惟一性

Global Partitioned Index和Local Partitioned Index是否都能保证数据的惟一性呢？比如，我们需要为主键约束创建一个索引（保证惟一性），应该选择创建什么索引的类型呢？

创建测试表：

创建一个Global Partitioned Index来作为主键约束的索引：

可以看到，主键约束是能够成功创建的。如果这个主键索引不是表的分区列呢？

这时选择Global Partitioned Index也是没有问题的。

看看Local Partitioned Index:

我们也可以成功使用Local Prefixed Index来保证主键惟一约束。

那么，如果索引是Local Nonprefixed Index呢？

结果是失败了，返回了ORA-14196错误。
看起来我们不能选择Local Nonprefixed Index来保证惟一性。结论对不对呢？

其实不是这样的，即使我们的创建的索引是Local Nonprefixed的，只要该索引包含表分区列，也是可以作为主键约束索引的，比如：

总结来说：除了那些分区键不是索引键一部分的Local Nonprefixed Index外，无论是Local Partitioned Index还是Global Partitioned Index，都是可以用来作为主键约束的索引的，并且，对于Global Partitioned Index，是不需要限制索引键一定是分区键的。

本文的主题是分区表上的索引选择，写这篇博文的目的不是要记住哪些场景必须选择什么样的索引，而是通过理解不同索引的具体特点：如Global Index更为灵活，Local Index可用性更高等...从而在实际使用中执导我们更合理得根据应用的特点选择合适的索引类型。