一个复杂的数据需求的创新优化（r12笔记第96天)）

   今天处理了一个蛮有意思的案例，正如我给开发同学所说的那样，方案有很多，但是我们需要明确需求之后，找到一个最合适的需求。

   业务同学反馈，数据库中有一个表数据量很大，因为要做一期活动，需要近期的数据，以前的旧数据可以考虑清理。清理多少旧数据呢，差不多是99%的量，数据量有多大呢，差不多两个亿。所以这个需求听起来蛮简单，但是业务同学明确希望能够保持业务的可持续性，这样一来就对实现方案有了一些选择。

   这个看起来简单的需求，我的总结如下：

总结下来，要做4件事情：

1. 优化查询，目前是基于时间范围来查询，经过评估需要给这个表添加索引

2. 清理数据，表里有两亿数据，但是要清理绝大部分数据。

3. 保证业务的可持续性，每10分钟会做一次统计分析，数据会实时录入系统

4. 把表修改为分区表，把旧数据放入一个分区，新数据放入另一个分区，变更之后删除就分区即可。

如此一来，在线重定义这个方案就是我评估之后的首选方案。

但是我发现，尽管我信息满满，但是在实践的时候是困难重重，过程还是略有坎坷，碰到了很多细小的问题，碰到了低版本的bug,能够峰会陆欢，化险为夷，看起来要做得事情还真不少。

在线重定义碰到的坑

在线重定义的过程步骤其实不难，但是比较纠结的是在这个过程中碰到了很多的问题，有些竟然是低版本的bug,这个时候我是深深怀念11g，平时感觉不到明显的好，但是这种问题面前就会感觉11g更加亲切。

除了时间和空间的代价较大之外，碰到了一些bug会让人实在有些无奈。

比如进行到90%左右的时候，估计到了最后的索引rebuild的时候去，抛出了temp空间不足的错误，之前的准备都白费了，重头开始。

在取消在线重定义的过程中，碰到了10g中的bug,导致abort的过程没有响应，系统CPU消耗很高，最后手工清理，杀掉会话解决。

在继续尝试在线重定义的过程中，碰到了10g中的bug,最后发现其中一个原因是由于回收站的影响，清理回收站里的对象继续。

再一次尝试，在临近尾声的时候抛出了ORA-00600的错误，毫无疑问这又是一个10g的bug.

Errors in file /U01/app/oracle/admin/xxx/udump/xxx_ora_25657.trc:
ORA-00600: internal error code, arguments: [kcbnew_3], [0], [4], [293213], [], [], [], []

问题到了这个时候，连创建一个要在线重定义的分区表都会抛出上面的错误，经过查询，这个bug的临时解决方案就是杀心bufer cache,听起来容易操作起来难，至少给业务同学解释这个风险点的时候，他们还是有顾虑，所以这个方案有待商榷。

问题总得解决，临时解决方案

 问题到了这个时候，我们算是给业务同学报忧不报喜了。在线重定义的方案不太可行，至少目前是碰到了一些问题，要临时解决还是存在风险点。

业务同学觉得我这个需求也不过分啊。能不能想想办法，或者这么来做，我就需要最近几天的新数据，做完活动之后就不用了，对数据权限没有要求，只需要查，压根不修改和删除。

于是有了这么一个设想，我们创建一个物化视图，然后增量刷新，commit后自动同步，这样一来就是一个影子表的感觉，在新的表上我们可以创建索引，这样查询的效率也可以提高。如下图所示。

这样一个方案好不好做呢，其实如果细究起来还是有一些难度，我们需要创建一个prebuilt的物化视图，然后选择性同步里面的部分数据。

   而另一方面业务同学如果要查询之前的那个大表，性能又很差，所以两者综合起来有什么改进方案呢，其中一个方案就是创建物化视图，全量刷新后，增量刷新，这样一来这个物化视图表就是源表的一个影子表，查询完全可以在这个表上来进行。如此一来业务放也就不着急去申请维护窗口去添加索引了。问题这样可以过渡一段时间。

所以方案就成了这样了。

    创建物化视图的过程当然也不是一帆风顺，花了些功夫，碰到了一些小问题，总算是给了业务同学一个基本满意的交代，原本的查询需要1分多钟，现在不到1秒钟就可以搞定，性能差异非常大。  

高手过招，就是不断优化

  当然这样一个解决方案，虽然能够交代了，其实我心里还是很遗憾的，因为最大的问题没有解决，那就是旧数据还是在那里。虽然查询效率提高了，但是从问题的本质来说，还是没有解决，只是缓解了。

   这个时候有一个好消息是，因为这个表的数据量比较大，已经刷新了buffer cache,所以就不需要我们手工来刷了。一个检查点就是创建分区表没有问题了，只是一个好消息。

   这个时候能不能考虑对源表进行在线重定义呢，显然不行，因为源表已经有了物化视图日志，在线重定义的基本条件就不满足了。

为此我就想了下面的方案。可以实现清理数据的这个需求，那就是偷天换日。

首先从物化视图中根据时间条件（有索引，所以性能高）把要保留的数据查出来，放入分区表SERVERLOG_PAR_OLD

我们使用exchange partition,把 SERVERLOG_PAR_OLD里的分区数据和SERVERLOG做交换，这样2个亿的数据就和分区的数据做了交换，然后可以把近期的增量数据通过物化视图的形式插入临时表serverlog_hot里面，最后把数据补入serverlog,这样就是一个完整的数据流了，而后期添加索引的操作这个时候影响面就很小了，可以使用在线重定义来完成，或者直接添加也可以（因为数据量小很多，速度很快）

  整个过程也算是有惊无险，还是充满挑战的。

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/23718752/viewspace-2140871/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/23718752/viewspace-2140871/