通过删除旧行自动强制最大数据库表大小 [英] Automatically enforcing a max database table size by dropping old rows

问题描述

我有一个Oracle表，其中包含应用程序的事件日志消息.我们不希望该表失去控制并占用太多空间，因此我们希望对它可以包含的行数设置上限(例如一百万).

I have an Oracle table which contains event log messages for an application. We don't want this table to grow out of control and eat up too much space, so we'd like to set a cap on the number of rows that it can contain to some big number like one million.

因此，我们想告诉Oracle，当表太大时，它应该删除最旧的行以为新行腾出空间.

So we'd like to tell Oracle that when the table grows too large, it should delete the oldest rows to make space for new rows.

有没有办法做到这一点?我想我们可以使用触发器或通过使用存储过程进行所有插入来做到这一点，但是有没有更简单的方法呢?

Is there any way to do this? I imagine we could do this with a trigger or by making all inserts with a stored procedure, but is there anything simpler?

一些答案提出了涉及分区的建议解决方案.尽管有必要，我们目前还没有对该表进行分区的功能.但是，从调查的角度来看，即使我们对表进行了分区，我们仍然需要某种计划的作业来删除旧的分区，依此类推.因此，我们决定放弃分区，而采用a计划的工作，每天检查一次行数并根据需要删除旧行.

A couple of answers have suggested solutions that involve partitions. We do not currently partition this table, although we have the ability to do so if necessary. However, from looking into the matter, it seems that even if we partition the table, we'd still need for some kind of a scheduled job to drop the old partitions, etc. So we've decided to forgo partitions in favor of a scheduled job to check the row count and delete old rows as necessary once per day.

谢谢大家的帮助.

推荐答案

取决于您对更简单...的定义.

Depending on your definition of simpler...

我的偏见是安排一个定期运行(例如每晚运行)的作业，以便删除最旧的行.如果您希望一天之内就可以生成一百万个事件日志，那么您可能希望这项工作更频繁地运行，但是对于大多数人来说，夜间通常就足够了.这具有异步的优势，这样您就不必每次进行插入来查找是否必须清除某些数据时都要计数一百万行的开销.另一方面，它确实需要一些管理人员来设置工作.

My bias would be to schedule a job that runs periodically (say, nightly) in order to delete the oldest rows. If you expect that you could generate a million event logs in a single day, you may want the job to run more frequently, but nightly is generally sufficient for most people. This has the advantage of being asynchronous so that you're not incurring the overhead of counting a million rows every time you do an insert to find out if you have to purge some data. On the other hand, it does potentially require some administration to set up the job.

这篇关于通过删除旧行自动强制最大数据库表大小的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！