关于分区技术的索引index

分区系列篇： 关于oracle分区技术--初了解 http://blog.csdn.net/wanghui5767260/article/details/39158873 关于分区技术的索引： http://blog.csdn.net/wanghui5767260/article/details/39181027 关于分区技术---索引 Index 一、 分区索引分类： 本地前缀分

分区系列篇：

关于oracle分区技术--初了解 http://blog.csdn.net/wanghui5767260/article/details/39158873

关于分区技术的索引： http://blog.csdn.net/wanghui5767260/article/details/39181027

关于分区技术---索引 Index

一、 分区索引分类：

本地前缀分区索引(local prefixedpartitioned index)

全局分区索引(global partitionedindex)

本地非前缀分区索引(localnon-prefixed partitioned index)

1.1 表和索引的组合：

第一种:表和索引都不分区

最简单的方式 就是常见的索引

第二种：表分区了，但索引没分区

这导致了：“我们已经做了分区表了，怎么性能没有提高?”的主要原因之一。

在很多系统中，特别是交易系统里面，是通过索引访问数据库的。如果索引没有分区，索引树的高度没有变，因此访问性能当然没有提高。如果按索引访问表，与表是否分区关系不大。

第三种：表没有分区，但索引分区了

索引只能是全局分区索引。

第四种：表分区了，索引也分区了(重点！！！)

全局分区索引

本地前缀分区索引

二、 具体介绍分区索引

本地分区索引

是指索引的分区方法与对应表的分区方法一样。

2.1本地前缀分区索引（local prefixed partitioned index）

是指分区字段是索引字段的前缀。

一张交易流水表(TXN_CURRENT)，并且以交易日期字段(TXN_DATE)按年度进行了范围分区。在TXN_DATE 字段创建索引。

Createindex idx_txn_current_1 on txn_current(txn_date) local;

或者复合分区索引

Createindex idx_txn_current_2 on txn_current(txn_date,area) local;

好处：

a. 由于分区索引和表分区对应，因此查询数据直接到对应的索引分区去查询，索引树的高度肯定低于非分区情况下的那棵大索引树了，也就是说性能更高了。

b. 当某个分区进行删除（DROP）或合并（MERGE）操作之后，oracle自动对所对应的索引分区进行相同的操作，整个本地前缀索引依然有效，不需要重建(rebuild)操作，这样大大保障了表的可用性。

2.2 全局分区索引(global partitioned index)

索引的分区与表分区无关

分区表按年份进行分区，在地区(AREA)字段上建立分区索引，

也就是说：杭州交易数据肯定可分布在各年份，湖州、嘉兴交易数据同样如此。

假如有这样的查询需求：

Select* from TXN_CURRENT where area=’05711001’;

--假设查询杭州西湖区

好处：

a. 此时oracle会很聪明地知道杭州的分区索引树上去检索，索引高度肯定低于非分区情况下的大索引树了，也就是性能更高了。

b. 在分区粒度比较细的情况下，性能甚至高于本地前缀分区索引。

不足：

a. 主要体现在高可用方面。如果该表的03年数据通过分区删除全部删除了。则全局分区索引(包括普通非分区索引)则全部失效(INVALID)，这些索引不可用了，除非重建(rebuild)操作。数据量越大，索引量也越大，重建索引时间也越长，无法通过该类索引访问数据的时间也越长。因此，大大降低数据的可访问性。

2.3 本地非前缀分区索引

可能导致性能会下降的情况，本地非前缀分区索引的性能可能还不如不分区。

优点：

a. 提高按索引访问的可用性！我们假设要通过删除分区技术，进行03年数据的清理，如果area字段索引建立成普通索引，或者是全局分区索引，都会面临一个问题：在分区删除(DROP)操作之后，普通索引和全局分区索引都会失效(INVALID)，必须重建。而本地非前缀分区索引的好处在于，在分区删除操作后，该本地非前缀分区索引依然有效。

三、 整理思路

理解分区索引藏宝图：

描述藏宝图：找宝藏

(1)如果表分区字段正好是索引字段或者是其前缀。例如：上述TXN_CURRENT 表分区字段是TXN_DATE，则TXN_DATE正好是索引字段（TXN_DATE），或者正好是索引字段（TXN_DATE,AREA）的前缀，则此时应建立local profixedpartitioned index.

(2)否则，如果欲将非分区字段建立为唯一索引，例如：假设在TXN_CURRENT 表的某个字段建立唯一索引，oracle要求必须global prefixed index.

否则报错：

ORA-14039:partitioning columns must form a subset of key columns of a UNIQUE index

(3)流程图再往下，此时要判断“是否性能在可承受范围，而分区的管理性、可用性更重要?”。如果是，就应建立local non-profixed index。也就是上面所描述的：如果历史数据的整理非常频繁，而且不能承受全局分区索引重建的长时间带来的索引不可用，同时日常交易性能尚能接受，则建议设计为本地非前缀分区索引。

(4)流程图再往下，最后判断系统是否为交易系统或者是数据仓库系统。因为通常情况下，数据仓库会有频繁的大批量数据导入（ETL）操作，以及历史数据清理操作，此时分区索引可用性更重要，因此建议设计为Localnon-profixed index 。而在交易系统中，日常查询性能要求更高，历史数据清理操作频度相对较低，因此建议设计为global profixed index。

注：oracle没有global non-profixed index概念。

四、 分区表设计建议 只是建议：更多看实际！！！

(1) 表的大小：当表的大小超过1.5G—2GB时，或对于OLTP系统，表的记录超过1000万条时，都应考虑对表进行分区。

(2) 数据访问特征：基于表的大部分查询应用，只访问表中的少量数据。对于这样的表进行分区，可充分利用分区技术排除无关数据查询的特征。

(3) 数据维护：按时间段删除成批的数据，例如按月删除历史数据。对于这样的表需要考虑进行分区，以满足维护的需求。

(4) 数据备份和恢复：按时间周期进行表空间的备份时，在分区与表空间之间建立起对应关系。

(5) 只读数据：如果一个表中的大部分数据都是只读数据，通过对表进行分区，可将只读数据存储在只读表空间中，对于数据库的备是非常有益的。

(6) 并行数据操作：对于经常执行并行操作（如parallelinsert、parallel update等）的表应考虑进行分区。

(7) 表的可用性：当对表中部分数据的可用性要求很高时，应考虑进行表分区。