需要从具有1.5TB数据的表中清除三分之一的数据-Needtopurgeonethirdofdatafromatablehaving1.5TBdata

2  

"Purge" = "remove" = "delete", I presume.

我猜想,“清除”=“删除”=“删除”。

You have several options; here are some of them:

你有几个选择;这里是其中的一些:

• Is the table partitioned? I guess not; otherwise, you'd just truncate (or drop) partitions you don't need any more, and it would be fast

  表分区了吗?我猜不会;否则,你只是截断(或删除)你不再需要的分区,而且速度很快

  • can you partition it now?

  你现在可以分区吗?

• Another option is to literally delete those rows. It'll create a large undo.

  另一种选择是逐字删除这些行。它会创建一个大的撤消。

  • this option is probably quite slow

  这个选项可能很慢

• Or, use CTAS (Create Table As Select) and create a new table with rows you'd want to keep; then truncate (or, if you can afford it, drop) the original table and move rows back (or, if you dropped it, rename newly created table to the old, original name). Depending on foreign key constraints, that might not be that simple.

  或者,使用CTAS(Create Table As Select)创建一个包含您想要保留的行的新表;然后截断(或者,如果你能负担得起,删除)原始表并将行移回(或者,如果你删除它,将新创建的表重命名为旧的原始名称)。根据外键约束,可能不那么简单。

  • this one takes additional space on a disk. Do you have it? What about constraints?

  这个占用磁盘上的额外空间。你有吗?限制怎么样?

• Or, delete rows in chunks, using loop in a PL/SQL procedure. In order to save some undo space, commit within a loop (not too frequently, though). ORA-01555 (snapshot too old) might appear

  或者,使用PL / SQL过程中的循环删除块中的行。为了节省一些撤消空间,在循环内提​​交(但不要太频繁)。可能会出现ORA-01555(快照太旧)

  • deleting piece-by-piece is OK if you aren't worried about "all or nothing". What if delete fails? Will it affect anything else? How?

  如果您不担心“全有或全无”,可以逐条删除。如果删除失败怎么办?它会影响其他什么吗?怎么样?

Which one is the best? That's a million dollars question, and I don't have an answer to it. If possible, import that table into your testing environment and ... well, test those options. Testing in production isn't recommended, right?

哪一个是最好的?这是一个百万美元的问题,我没有答案。如果可能,将该表导入您的测试环境中......并且,测试这些选项。不推荐在生产中进行测试,对吗?

0  

We have the same problem at work. Our plan of attack is to do an online redefinition to partition the table. Once that is done, the relevant partitions can be dropped.

我们在工作中遇到同样的问题。我们的攻击计划是进行在线重新定义以对表进行分区。完成后,可以删除相关分区。

We are currently setting up a copy of the database on a test machine to test the approach.

我们目前正在测试机器上设置数据库的副本以测试该方法。