【基础知识】Oracle分区表及索引解读

1、分区表

Oracle提供了分区技术以支持VLDB(Very Large DataBase)，关于什么时候需要分区表，官网有如下2个建议：

1、Tables greater than 2GB should always be considered for partitioning.

2、Tables containing historical data, in which new data is added into the newest partition. A typical example is a historical table where only the current month's data is updatable and the other 11 months are read only.

Oracle的分区表如图1所示，通过对分区列判断，把分区列不同的记录，放到不同的分区中，每个分区都是一个独立的段（SEGMENT），可以存放到不同的表空间中。分区对应用完全透明，查询时可以通过查询表来访问各个分区中的数据，也可以通过在查询时直接指定分区的方法来进行查询。

图1 分区表图示

分区类型有很多，如范围分区（range）、哈希分区（hash）、列表分区（list）、范围-哈希复合分区（range-hash）、范围-列表复合分区（range-list）等。

分区优点有：

1、由于将数据分散到各个分区中，减少了数据损坏的可能性；

2、可以对单独的分区进行备份和恢复；

3、可以将分区映射到不同的物理磁盘上，来分散IO；

4、提高可管理性、可用性和性能。

2、 分区索引

2.1 全局索引

全局索引是在全表上创建索引，可以依赖普通的表，也可以依赖分区表建立。全局索引也可以分区，索引分区的机制不依赖于底层表的分区机制，可以创建自己的分区，索引的分区键可以选择跟表的分区键不一致，每个索引分区的键值可指向任何表分区。对于全局分区索引，其索引键前缀需包含索引的分区键，即“全局前缀索引”，全局分区索引基于“前缀索引”来保证主键的惟一性，即使不包括表的分区键，也能保证惟一性的分区索引。

图2 全局分区索引图示

图3 全局非分区索引图示

2.2   本地索引

本地索引不能对普通表建立，只能依赖分区表建立，并且是依赖分区表的分区键来建立，即依赖底层表的分区机制来建立索引。随着表分区，建立一一对应的索引分区，每个索引分区中的条目都只指向一个表分区，互相独立，自动维护。若将表分区键定义在索引第一列上，称为“前缀索引”。若索引不以表分区键作为它的索引字段的第一列，或者不包含分区键，称为“非前缀索引”。前缀索引根据分区键可以准确的判定出数据所在的分区，而非前缀分区索引因为不包含分区键，会因判定不出分区中是否有需要的数据而进行全分区扫描，因此一般不建议使用“非前缀索引”。

图4 本地索引图示

2.3   全局索引与本地索引

OLAP系统中大多使用本地索引，OLTP系统更多会使用全局索引。下面从3个角度来进行索引优缺点比较：

1、可用性：本地索引可用性更好，一个索引分区出问题不会影响其他索引分区，而全局索引很可能会成为一个故障点，一旦出现问题则整个索引都不可用。

2、维护性：本地索引更好维护更灵活，对分区的操作只影响本分区的索引，只需要重建与维护一个索引分区；对全局索引，很多情况下都需要重建。

3、性能：若符合查询条件的索引键值仅涉及单个分区索引，则使用分区的本地索引可能会比使用全局索引效率更高，因为搜索的范围更小。若符合查询条件的索引键值跨越多个分区索引，因为搜索分区索引时需要来回切换多个分区的数据块，使用全局索引可能会比使用分区索引效率更高。

一般来讲，在需要频繁删除表的分区且数据更新较为频繁时，为了维护方便，应尽量避免使用全局索引。若是存在较多跨分区查询，最好使用全局索引。

3、分区操作对索引的影响及解决方法

在DBA的维护管理中，索引失效是一个常见的问题，引起全局索引失效的数据库操作有：

ADD PARTITION | SUBPARTITION (hash)

COALESCE PARTITION | SUBPARTITION (hash)

DROP PARTITION | SUBPARTITION

EXCHANGE PARTITION | SUBPARTITION

MERGE PARTITION | SUBPARTITION

MOVE PARTITION | SUBPARTITION

SPLIT PARTITION | SUBPARTITION

TRUNCATE PARTITION | SUBPARTITION

引起本地索引失效的数据库操作有：

COALESCE PARTITION

EXCHANGE PARTITION

MERGE PARTITION

MOVE PARTITION

SPLIT PARTITION

注：若操作一个空分区，对全局索引和本地索引均无影响（除EXCHANGE PARTITION外）。

可以通过查询dba_indexes 、user_indexes、user_part_indexes和 user_ind_partitions视图来查看索引是否有效，例如：select  INDEX_NAME,STATUS from dba_indexes where table_name='TABLE_NAME'。

对于索引失效问题，Oracle提供了2种处理方法：

1）Update indexes

2）Rebuild，本地索引不能整体重建，只能每个分区重建。

这2种方法各有利弊，在生产上不同的环境方法也不一样，需要根据生产实际情况，平衡操作ddl的时间和重建索引操作的时间，以决定使用哪种方法。一般当数据量较大时，重建索引花费巨大，会严重影响查询性能，此时常常采用操作分区后直接update 索引的方式。

此外，EXCHANGE PARTITION操作是个很特殊的操作，加上update indexes参数，EXCHANGE PARTITION依然会造成本地索引失效，必须rebuild本地索引。