代码之家 › 专栏 › 技术社区 › Steve Broberg

oracle数据库中旧数据的删除技术

purge partitioning oracle

6

Steve Broberg · 技术社区 · 17 年前

我们有一个成熟的Oracle数据库应用程序(在生产中已经超过10年了),在此期间,我们一直在使用自己设计的脚本来删除不再需要的旧数据。它们的工作方式是对适当的表发出delete语句,在一个频繁提交的循环中执行,以避免用i/o重载系统或使用过多的撤消空间。

他们大部分工作都很好。它们每天运行,从系统中删除最旧的数据需要大约一个小时。我关心的主要问题是所有这些删除操作可能对表和索引产生的影响,以及这样一个事实,即即使它们不会过度加载系统,但在这么短的时间内删除一天的数据确实会产生耗尽实例缓冲区缓存的效果,从而导致随着缓存的逐渐恢复,t查询在接下来的几个小时内运行速度会稍慢。

多年来我们一直在考虑更好的方法。在过去,我听说人们使用分区表来管理旧数据,例如每个分区一个月,每个月删除一个最旧的分区。这种方法的主要缺点是我们的收获规则超出了“删除X月”的范围。允许用户根据关键值指定数据必须在系统中保留多长时间(例如,在发票表中,可以在3个月后删除account foo,但account bar可能需要保留2年)。

还有一个引用完整性的问题;oracle文档主要讨论在数据仓库的上下文中使用分区清除数据,其中的表往往是超立方体。我们的方法更接近于oltp的结尾,而且x月的数据与y月的数据有关系是很常见的,为这些表创建正确的分区键充其量只是个小问题。

至于缓存溢出,我读过一些关于设置专用缓冲缓存的内容,但似乎更多的是基于每个表,而不是基于每个用户或每个事务。为了保留缓存,我真的希望收割作业在任何时候都只在缓存中保留一个事务值的数据,因为一旦删除,就不需要保留数据。

在可预见的将来,我们是在使用删除,还是有其他更聪明的方法来处理收割?

4 回复 | 直到 17 年前

1

4

David Aldridge 17 年前

在很大程度上,我认为你一直在做删除。

您对在您的案例中使用分区的困难的评论可能会阻止它们被有效地使用(根据记录的类型使用不同的删除日期),但是您可以在您可以分区的记录上创建一个“delete date”列吗?这样做的缺点是更新成本很高,因为更改删除日期可能会导致行迁移,所以您的更新实际上是作为delete和insert实现的。

由于引用完整性问题,即使这样,您也不能使用DDL分区操作来删除旧数据,但是分区仍然可以用于对要删除的行进行物理群集,以便需要修改的块更少,以便删除它们,减轻对缓冲区缓存的影响。

2

0

kdgregory 17 年前

删除也没那么糟, 如果重新生成索引 . Oracle将恢复不再包含数据的页面。

但是,从8i开始(很可能仍然如此),它将无法正确恢复不再包含有效引用的索引页。更糟糕的是,由于索引叶是链接的,您可能会遇到这样的情况:它将开始遍历叶节点以查找行。这将导致性能显著下降:通常需要几秒钟的查询可能需要几分钟。下降也很突然:有一天会好,第二天不会。

我在一个使用了增加密钥和定期删除数据的应用程序中发现了这种行为(甲骨文有一个bug,其他人也有)。我们的解决方案是反转部分密钥,但这对日期没有帮助。

3

0

CÄtÄlin PitiÈ 17 年前

如果临时停用索引,执行删除操作,然后重新生成索引,会怎么样?它会提高删除的性能吗?当然,在这种情况下,必须确保脚本正确,并确保正确的删除顺序和引用完整性。

4

0

slovon 17 年前

我们有同样的问题,使用同样的策略。如果情况变得非常糟糕(索引、表的分配非常零碎,…),我们将尝试应用空间回收操作。

表必须允许行移动(类似于闪回): alter table ttt启用行移动; 改变表ttt缩小空间; 然后重建所有索引。

我不知道你对维护窗口是怎么看的,如果应用程序必须一直可用,那就更难了,如果不是,你可以在离线时做一些“重新打包”。alter table ttt move tablespace sss“在重写表时,会做很多工作来清理混乱。您还可以指定新的存储参数,如数据块管理、大小…看一下文件。

我使用这样的脚本为整个数据库创建脚本:

SET SQLPROMPT "-- "
SET ECHO OFF
SET NEWPAGE 0
SET SPACE 0
SET PAGESIZE 0
SET FEEDBACK OFF
SET HEADING OFF
SET TRIMSPOOL ON
SET TERMOUT OFF
SET VERIFY OFF
SET TAB OFF
spool doit.sql
select 'prompt Enabling row movement in '||table_name||'...'||CHR (10)||'alter table '||table_name||' enable row movement;' from user_tables where table_name not like '%$%' and table_name not like '%QTAB' and table_name not like 'SYS_%';
select 'prompt Setting initial ext for '||table_name||'...'||CHR (10)||'alter table '||table_name||' move storage (initial 1m);' from user_tables where table_name not like '%$%' and table_name not like '%QTAB' and table_name not like 'SYS_%';
select 'prompt Shrinking space for '||table_name||'...'||CHR (10)||'alter table '||table_name||' shrink space;' from user_tables where table_name not like '%$%' and table_name not like '%QTAB' and table_name not like 'SYS_%';
select 'prompt Rebuilding index '||index_name||'...'||CHR (10)||'alter index '||index_name||' rebuild;' from user_indexes where status = 'UNUSABLE';
spool off
prompt now check and then run @doit.sql
exit