【DB笔试面试554】在Oracle中，分区索引分为哪几类？

♣

题目部分

在Oracle中，分区索引分为哪几类？

♣

答案部分

索引按照是否分区可以分为分区索引（Partitioned Indexes）和非分区索引（NonPartitioned Indexes），如下图所示：

图 3-12 分区索引

与分区表类似，分区索引被分解成更小、更易于管理的索引片断。分区索引提高了可管理性、可用性、和可扩展性。分区索引根据索引列是否包含分区键及分区键是否是索引的引导列可以分为有前缀的分区索引和无前缀的分区索引。有前缀的分区索引指的是包含了分区键，并且将其作为引导列的索引。无前缀的分区索引的列不是以分区键开头，或者不包含分区键列。

分区索引就是简单地把一个索引分成多个片断。通过把一个索引分成多个片断，可以访问更小的片断（也更快），并且可以把这些片断分别存放在不同的磁盘上，从而避免I/O问题。B-Tree和位图索引都可以被分区，而HASH索引不可以被分区。可以有好几种分区方法：表被分区而索引未被分区；表未被分区而索引被分区；表和索引都被分区。不管采用哪种方法，都必须使用基于成本的优化器。有两种类型的分区索引：本地分区索引和全局分区索引。每个类型都有两个子类型，有前缀索引和无前缀索引。表各列上的索引可以有各种类型索引的组合。如果使用了位图索引，那么就必须是本地索引。索引分区最主要的原因是可以减少所需读取的索引的大小，另外把分区放在不同的表空间中可以提高分区的可用性和可靠性。在使用分区后的表和索引时，Oracle还支持并行查询和并行DML。这样就可以同时执行多个进程，从而加快处理SQL语句。

（一）本地分区索引（Local Partitioned Indexes）

本地分区索引也叫局部分区索引。在本地分区索引中，索引基于表上相同的列来分区，与表分区具有相同分区数目和相同的分区边界。每个索引分区仅与底层表的一个分区相关联，所以，一个索引分区中的所有键都只引用存储在某个单一表分区中的行。通过这种方式，数据库会自动同步索引分区及其关联的表分区，使每个表和索引保持独立。

本地分区索引在数据仓库环境中很常见，它有以下优点：

l 因为使分区中的数据无效或不可用的操作只会影响当前分区，这有助于提高可用性。

l 简化了分区维护。当移动一个表分区，或当某个分区的数据老化时，只须重建或维持相关联的本地索引分区。而在全局索引中所有索引分区必须被全部重建或维护。

l 如果分区发生时间点恢复，那么可以将局部索引恢复到指定的恢复时间，而不需要重建整个索引。

本地分区索引其分区形式与表的分区完全相同，依赖列相同，存储属性也相同。对于本地分区索引，其索引分区的维护自动进行，也就是说，当执行ADD、DROP、SPLIT或TRUNCATE的时候，本地分区索引会自动维护其索引分区。本地分区索引的分区属性完全继承于表的分区属性，包括分区类型，分区的范围值既不需指定也不能更改。对于本地索引的分区名称，以及分区所在表空间等信息是可以自定义的，例如以下语句创建的是本地分区索引，且每个分区对应于不同的表空间：

CREATE INDEX IDX_PART_RANGE_ID ON T_PARTITION_RANGE(ID) LOCAL (
  PARTITION I_RANGE_P1 TABLESPACE TS_DATA01,
  PARTITION I_RANGE_P2 TABLESPACE TS_DATA02,
  PARTITION I_RANGE_P3 TABLESPACE TS_DATA03,
  PARTITION I_RANGE_PMAX TABLESPACE TS_DATA04
  );

本地分区索引可分为以下类别：

n 本地前缀索引（Local Prefixed Indexes）在这种情况下，分区键处于索引定义的前导部分。

n 本地非前缀索引（Local Nonprefixed Indexes）在这种情况下，分区键不是索引列列表的前导部分，甚至根本不必在该列表中。

这两种类型的索引都可以充分利用分区消除（也称为分区剪除），此时，优化程序将不予考虑无关分区，以加快数据访问速度。查询是否可以消除分区取决于查询谓词。使用本地前缀索引的查询始终允许索引分区消除，而使用一个本地非前缀索引的查询则可能不会利用到分区消除。

（二）全局分区索引

全局索引（Global Index）既可以分区（全局分区索引），也可以不分区（普通索引），既可以建RANGE分区，也可以建HASH分区，既可创建于分区表上，也可以创建于非分区表上，就是说，全局索引是完全独立的，因此，它也需要更多的维护操作。

全局分区索引是一个B-Tree索引，其分区独立于所依赖的基础表。某个索引分区可以指向任意或所有的表分区，而在一个局部分区索引中，索引分区与分区表之间却存在一对一的配对关系。全局分区索引是通过指定GLOBAL参数指定的。本地分区索引比全局分区索引更容易管理，但是全局索引比较快。本地索引肯定是分区索引，但是全局索引可以选择是否分区。如果分区，那么只能是有前缀的分区索引，Oracle不支持无前缀的全局分区索引。

另外，如果对分区进行维护操作时不加上UPDATE GLOBAL INDEXES的话，那么会导致全局索引变为无效状态，所以，必须在执行完维护操作后重建全局索引。

关于全局索引，需要注意以下几点内容：

①　全局索引可以是分区索引，也可以是不分区的索引，全局索引必须是前缀索引，即全局索引的索引列必须是以索引分区键作为其前导列。

②　全局索引可以依附于分区表，也可以依附于非分区表。

③　全局分区索引的索引条目可能指向若干个分区，因此，对于全局分区索引，即使只截断一个分区中的数据，也需要REBULID若干个分区甚至是整个索引。

④　全局索引多应用于OLTP系统中。

⑤　全局分区索引只按RANGE或者HASH分区，HASH分区是Oracle 10g以后才支持的。

⑥　在Oracle 9i以后对分区表做MOVE或者TRUNCATE的时候可以用UPDATE GLOBAL INDEXES语句来同步更新全局分区索引，用消耗一定资源来换取高度的可用性。

⑦　若在表中使用A列作分区，但在索引中用B列作本地索引，若WHERE条件中用B来查询，那么Oracle会扫描所有的表和索引的分区，成本会比分区更高，此时可以考虑用B列做全局分区索引和用A列做本地索引。

⑧　在创建索引时，如果不显式指定GLOBAL或LOCAL，那么默认是GLOBAL。

⑨　在创建GLOBAL索引时，如果不显式指定分区子句，那么默认不分区。

⑩　含有子分区的分区索引有大小，但是在数据字典视图中的列SEGMENT_CREATED的值显示为N/A，STATUS的值也显示为N/A。

有关分区索引的一些数据字典视图如下所示：

l DBA_PART_INDEXES 分区索引的概要统计信息，可以得知每个表上有哪些分区索引，分区索引的类型是LOCAL还是GLOBAL。

l DBA_IND_PARTITIONS 每个分区索引的分区统计信息。

l DBA_INDEXES和DBA_PART_INDEXES 可以得到每个表上有哪些非分区索引。

本文选自《Oracle程序员面试笔试宝典》，作者：李华荣。