Oracle性能优化读书笔记(3)-Oracle表分区技术

Oracle表分区技术

分区前后处理上的差异

1.大批量数据清理

2.查询操作

3.高可用性方面，数据可管理性

技术概述

1.范围（range）分区

按对一张表指定的字段值或多个字段值范围进行分区

 2.Hash分区

通过oracle内部hash分区算法，将指定字段名输入进行散列运算，以此为标准均匀分布到各个分区

 为保证数据散列分布均匀化，一般hash分区为2的幂

特点：总体性能最佳，不会有特别大的分区，分布均匀。以哈希分区字段进行访问，所有记录访问性能相当

           使用oracle内部哈希算法，实施简单

            适用于静态数据。此类数据一般永久储存在数据库中不需要迁移。

局限：不适合大批量数据迁移和删除

3.列表（list）分区

 4.组合分区

    几种分区技术选取两种组合，11g大幅增强

5.间隔分区（11g）

    指定时间间隔如月，系统会在将来时间自动生成按月产生的分区

6.基于虚拟列的分区技术（virtual column_based）

    支持在字段函数基础上直接进行分区，而不需要新建一个分区字段通过触发器维护

7.引用（reference）分区

    针对主表通过外键关联了子表的情况，根据主表的分区自动凭借外键对子表进行相同的分区，一体管理无需分开设计。

分区索引技术

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

    假设分区表为交易流水表，以交易日期字段按年度进行范围分区，欲创建交易日期字段的索引

     所谓本地，是指索引分区方法和表分区方法一致。数据表按年度分成若干份后，索引也按同上方法分成了相等数量，索引树高度降低了。如下

     所谓前缀是指分区字段是索引字段的前缀。例如可以在日期和地区字段建立复合索引，仍为前缀索引

     除了性能提高外，本地前缀分区索引还有一个好处，某一个分区表drop或merge后，其他索引仍为有效状态，无需进行重建索引工作。

2.全局分区索引（globle partitioned index）

     比如在area字段建立索引

     全局分区索引示意图如下

     三个索引分别是三个地区。也就是说杭州的数据可以分布在各个年份。此时按地区查询数据时，索引树高度就下降了。全局索引在大部分情况下性能都非常好，有些情况甚至优于本地前缀索引。

    之所以叫全局索引，是因为索引分区的字段和表分区字段无关，如上例地区和日期字段的差别。

    缺陷：数据高可用性方面差，加入某年数据删除了，当前全局索引全部失效，需要rebuild，索引量越大重建时间越长，大大降低数据可访问性。

3.本地非前缀分区索引（local non-prefixed partitioned index）

    还是建立地区字段的索引，我们建在本地模式上，即使用local的同时，索引依据字段和表分区字段又不一样，就是本地非前缀索引了

     为何要这样做？这样做的弊端显而易见，本地意味着索引分区同样按分区表字段即日期进行划分，所以我们每个年份的划分的索引中包含当年所有的地区索引。当我们查询某一地区A维度的数据时，需要从每个数据块（被年份划分）中查询，因为地区A的数据可能在任一时间块中存在！这时候查询效率甚至比分区前还低。就很尴尬了。

    但是这样做的好处是同本地前缀类似，大大提高了按索引访问数据的可用性，如果我们要维护数据块，比如drop掉2010年的数据，通过这种索引方法就不用再重新建立失效的索引了。

    注：其实本地非前缀索引效率也没有那么不堪，当查询条件为符合语句时，Oracle会智能选择部分符合条件的索引进行查询。单一如地区维度这种就没办法了。。

分区索引建立的建议

    如果日常查询性能很重要，清理和维护历史数据不甚频繁，推荐设计全局分区索引。如果清理数据频繁且不能承受重建索引的代价，则推荐本地非前缀索引咯。

 更神奇的分区技术

    需求：假设有一张保存历史交易数据的history表，按年分区，现在要把实时交易记录的current表03年数据全部导入history表，并删除current表03年数据。这是典型的历史数据清理需求。如何实现

    境界1-菜鸟

    境界2-进阶

    境界3-牛人（使用exchange）

     创建过渡表stage，通过oracle交换技术几乎瞬间将p_2003分区数据交换到stage表中，再使用同样技术交换至目标表。神奇之处在于exchange并没有搬动数据，而是将p_2003分区与stage表物理段位置在数据字典中进行了交换。所以实际效率与数据量没有关系。

    exchange其他应用：如果要对一个分区超大表的某一分区进行数据批处理时，最好的办法是将这一分区交换至外面一张普通表，对其处理完后再交换回去。