Oracle试验分区过程结果及分析

为了能试验本文预测的效果,于是我在我本机腾出了30G的空间,将库置于非归档模式，然后用database link以insert append的方式直接加载数据。通过仔细的权衡，我创建的分区表如下（限于时间关系，将所有表分区和索引分区建在一个表空间内）：

–ddl过长，省略(见附件)

（注：复合分区可以为二级分区创建一个template，从而减少建表DDL的篇幅）

考虑只有本文开头的两个查询问题突出，因此我只建立了俩索引，选择了全局范围分区索引。

注：分区索引也可以使用本地前缀索引，可以减少DDL篇幅

考虑到D_PRODATETIME的值较多，当查询来临时，oracle首先以查询where条件中的D_PRODATETIME进行裁减，到目标分区之后，如果有索引的话，应该可以进行INDEX RANGE SCAN进行扫描，因此先建立分区索引试试。同样选择了全局范围分区索引。

下面是加载数据以及实施步骤：

l  表数据加载

创建db link “ctn”.

Insert /*+append*/ into b_ctn_normal select * from b_ctn_normal@ctn;

加载速度还可以，大概30分钟加载完,加载数据量4400W。

l  分别按照上述的策略创建分区索引

GLOBAL_INDEX_D_VERIFYDATETIME和GLOBAL_INDEX_D_PRODATETIME

索引创建约为30分钟

l  执行sql1和sql2和原始库进行对比

Sql1的测试结果对比：

Sql2的测试结果对比：

通过以上对比结果，显示新的分区策略带来了巨大的性能提升，显示了oracle分区技术的强大威力。原来十几分钟甚至返回不了结果的查询现在毫秒就返回数据。

下面分析优化后的执行计划：

Sql1执行计划:

通过执行计划可以看出，查询正确的使用了d_prodatetime字段进行了分区裁减，然后使用到了该列的分区索引，但是并没有使用n_madein进行裁减。于是改变了下查询条件，将查询的数据量增大：

这次，查询使用了二级分区裁减。先是对一级分区进行裁减，然后又对二级分区进行裁减，最后对二级分区使用N_MADEIN进行全表扫描。执行计划显示，查询5000条数据时耗时增加了很多，因为扫描的数据量实在太大了，查询需要扫描很多分区。这样只能通过减少一次查询的数据量来保证性能。通过和开发人员确认，一次查询一般不用返回这么多数据。

Sql2执行计划:

执行计划已经显示的很明确，一级分区按照新分区的字段进行裁减，然后使用建立的分区索引，性能很高。

虽然新的分区策略显示了巨大的性能提升，有效的解决了性能问题，但是仔细分析一下，仍然存在一些问题：

u  分区较多，在4K万级别的表上，分区多达493个，这有些过分了。需要减少分区数量。目前的分区是每俩月一个分区，目前的数据分布比重新分区前均匀了很多，但是仍然存在不均匀现象，而且每俩月一个分区仍然较多。因此需要维持现在的范围分区字段不变，将现在的俩月一个分区的条件变化一下，分析数据的分布区间，制定一个不均匀的分区条件。如2010年8月的数据很多，那可以分别以2010-08-01~2010-08-15~2010-08-30为区间划分。如果2010年9-12月数据很少，那么可以将9-12月合并为一个分区。尽可能的均匀划分分区记录数，也减少分区数量。

u  评估二级分区的必要性。总的分区数是1级分区和二级分区的乘积，为M*N的关系。二级分区的增加，大大增加了分区数。分析发现，有接近一半的二级分区是空闲的，并没有记录装入，浪费了大量的空间。而且目前的sql并没有使用到二级分区裁减，因此需要评估二级分区带来的性能提高。然后考虑是否将二级分区去掉只采用范围分区。去掉二级分区，目前对性能是没有坏处的，而且未来如果用到对N_MADEIN字段的裁剪，直接alter表即可增加二级分区，不用重建。因此建议去掉。

l  总结

分区是处理大表的首要应对策略，但是分区字段的选取和分区的方法需要仔细权衡，一般第一想到的分区字段都是合理的，但是一些隐含的字段没有考虑到，未来数据量上去了，这些隐含的条件造成的性能问题就暴露出来了，因此还是需要全面的分析。

对表进行了分区，相应的也要对索引进行分区，这样可以裁减掉部分索引,然后裁减掉记录,虽然是海量数据,但是却拥有极高的查询速度。记得在一本书上看过，作者说，正是因为有了分区技术，oracle才敢号称是海量数据库。

转自：https://www.cnblogs.com/zhangxsh/p/3494420.html