sql - 从 Oracle 中的一个非常大的记录集中选择一个记录子集耗尽内存

Question

我有一个将日期从 GMT 转换为澳大利亚东部标准时间的过程。为此，我需要从数据库中选择记录，对其进行处理，然后将它们保存回来。

要选择记录，我有以下查询:

SELECT id,
  user_id,
  event_date,
  event,
  resource_id,
  resource_name
FROM
  (SELECT rowid id,
    rownum r,
    user_id,
    event_date,
    event,
    resource_id,
    resource_name
  FROM user_activity
  ORDER BY rowid)
WHERE r BETWEEN 0 AND 50000

从总共大约 50000 行中选择一个 block 。 6000 万行。我将它们分开是因为 a) Java(写入更新过程的内容)因行太多而内存不足(每行都有一个 bean 对象)和 b) 我只有 4 gig 的 Oracle 临时空间可以玩和。

在此过程中，我使用 rowid 来更新记录(因此我有一个唯一值)和 rownum 来选择 block 。然后我在迭代中调用此查询，选择接下来的 50000 条记录直到没有剩余(java 程序控制它)。

我遇到的问题是这个查询的 Oracle 临时空间仍然不足。我的 DBA 告诉我不能授予更多的临时空间，因此必须找到另一种方法。

我已经尝试用 View 替换子查询(我假设是使用排序的所有临时空间)，但是使用 View 的解释计划与原始查询之一相同。

有没有不同的/更好的方法来实现这一目标而不会遇到内存/临时空间问题？我假设使用可用临时空间更新日期的更新查询(与 Java 程序相反)会遇到同样的问题吗？

非常感谢您在这方面的帮助。

更新

我按照下面的建议沿着 pl/sql block 的路径走下去:

declare
  cursor c is select event_date from user_activity for update;
begin
  for t_row in c loop
    update user_activity
      set event_date = t_row.event_date + 10/24 where current of c;
    commit;
  end loop;
end;

但是，我的撤消空间用完了。我的印象是，如果在每次更新后提交，那么对撤消空间的需求就很小。我的这个假设不正确吗？

Answer 1

单个更新可能不会遇到同样的问题，而且可能会快几个数量级。大量的临时表空间只是为了排序才需要的。尽管如果您的 DBA 对临时表空间如此吝啬，您可能最终会用完 UNDO 空间或其他东西。 (看看ALL_SEGMENTS，你的表有多大？)

但是如果你真的必须使用这种方法，也许你可以使用过滤器而不是排序方式。创建 1200 个桶并一次处理一个:

where ora_hash(rowid, 1200) = 1
where ora_hash(rowid, 1200) = 2
...

但这会非常非常慢。如果值在过程中途发生变化，会发生什么情况？一条 SQL 语句几乎肯定是执行此操作的最佳方式。