Oracle开发建议系列第五期 ——分区表的使用

作者：周鹏

分区表可以改善数据库的可管理性、性能和可用性，恰当的使用分区表可以为程序开发和业务实现带来很大好处。

哪些表应该分区呢？

大表和繁忙表尽量使用分区。凡记录数超过1千万条，或大小（记录数*平均行长度）超过10G的表，被视为大表。大表尽量进行分区，目的在于避免过大的segments的出现，大segments不利于管理和提升高可用性。繁忙表应进行HASH分区，分区目的在于避免热点块的出现，让同时并发的操作分散在更多的分区上，这对于提升性能至关重要。

分多大合适呢？

单表分区时一般建议如下：
1、 每个最细粒度的分区（无子分区的表，指分区partition，有子分区的表，指子分区subpartition，下同）应该控制在每分区不超过1千万条记录；
2、 每个最细粒度的分区大小（记录数*平均行长度）不超过10G。
3、 HASH分区时分区的数量应该为2的幂数。

历史表优先选择按时间范围分区，如果按天分区后分区中的记录数仍然很多，则应选择按如账户或手机号等选择性高的字段进行HASH子分区。

选择分区字段要注意什么呢？

1、 范围分区字段应该尽量只选择单个字段，多个字段将加大后期维护复杂度，同时也不利于数据库实现分区消除。
2、 该字段不得有空值。
3、 该字段的值不会被UPDATE，造成行迁移。
4、 范围分区时，所提供分区标准和字段类型保持一致。比如避免给Number类型字段提供char类型的分区范围。
5、 不得在分区时对分区字段进行各种运算，否则将造成效率下降。比如不得使用substr函数等；避免写为类似to_char(TX_DATE,’yyyy mm’) >=’2015 12’,而应该写为TX_DATE >=to_date(’2015 12’,’yyyy mm’)。
6、 选择字段要考虑各分区数据量平衡，尤其HASH分区的字段选择性要高，以保证各分区平衡。

还有哪些问题需要注意呢？

（1）分区不是越多越好，要控制分区数量
若全库分区过多，数据字典会特别庞大。会造成数据库整体运行效率明显下降。所以建议联机交易表单表分区数不超过256个，历史表单表分区数不超过1024个，全库不超过24万个分区。
（2）分区创建时间要合理规划，避免数据库其他操作的影响。
创建分区的任务要单独设置，以固定其执行时间，避免受其他跑批任务影响而造成时间不固定。特别注意，要避开统计信息收集时间，防止因持锁阻塞影响分区创建，导致影响业务。范围分区必须提前创建，并在使用前要有检查、补建机制，避免要使用时发现不存在所需分区。
（3）INTERVAL分区尽量不用
INTERVAL分区是一种特殊的范围分区，当业务上有相应范围的记录插入时才创建所需分区，但容易造成如下问题：
新分区缺少统计信息，容易造成SQL语句全表扫描导致生产事故；新分区创建时间不固定，容易与统计信息收集发生资源争用，影响生产，而且容易产生BUG。因此对于INTERVAL分区，敬而远之，能不用就不用，有需求的可直接使用范围分区提前创建所需分区。