Oracle 超大表中partition和 subpartition的使用案例

Oracle 中 partition和 subpartition 是超大表常用的两个关键字，主要用来分区是提高数据读取效率。
（有关分区种类及语法细节网上有很多文章可参考）
今天看到一个应用方案，使用范围分区与业务处理结合到一起。感觉不错，记录下大致思路。

  

【需求】：

表中每日数据量较大，旧数据需要每日清理，不同种类的数据保存期不同。  
很明显如果每日用delete清除数据效率很低且麻烦。（还要考虑批量提交问题）  

 

【思路】： 
 将种类1绑定到partion的分割条件上，日期（日间隔）绑定到subpartition的分割条件上。
 （为使建表语句条件表达式不至于太复杂，使用虚拟列简化条件） 
 绑定完成后，大批量的操作都是针对subpatition来进行，即先查出对象数据的 distinct object_id(即subpatition) 
 然后使用下面的命令执行批量操作。如： 
  删除  Alter table [表名] drop subPartition [subPartition名]
  压缩  Alter table [表名] Modify subPartition [subPartition名] COMPRESS
  导出 exp user/pasword tables=[表名]:[Partition名]…
  
【优点】：

数据分区直接与数据的逻辑块一致，旧数据清除，备份和压缩等批量操作效率会有数量级的提升。

 

【缺点】：

应用场景较为有限（只能绑定两级），不过大多数场合应当说足够了。
虚拟列的引入对插入性能有损失（较轻微）。
还有就是要写一个PL/SQL来实现功能。

 

【留意点】：

当一个partition 只剩最后一个subpartition时使用上面的命令会抛出异常
这时必须捕获该异常并使用删除其所在的partition才行。（很奇怪Oracle为什么不把它作为bug）