从Oracle中非常大的记录集中选择记录子集将耗尽内存

Question

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

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

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行的块中选择块，请执行以下操作。六千万行。我之所以将它们分开，是因为( a) Java (编写更新过程所用的内容)内存不足，有太多行(每行我有一个bean对象)，而b)我只有4G的Oracle临时空间可玩。

在这个过程中，我使用rowid更新记录(所以我有一个唯一的值)，并使用rownum来选择块。然后，我在迭代中调用这个查询，选择下一个50000条记录，直到没有任何记录( java程序控制这一点)。

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

我尝试用一个视图替换子查询(我假设它使用了所有的临时空间)，但是使用视图的解释计划与原始查询中的一个完全相同。

是否有不同的/更好的方法来实现这一点，而不会遇到内存/诱惑空间的问题？我假设一个更新查询来更新日期(相对于java程序)，那么使用可用的临时空间也会遇到同样的问题吗？

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

更新

我沿着pl/sql块的路径走下去，如下所示：

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对临时表空间如此吝啬，但最终可能会耗尽撤消空间或其他东西。(看看ALL_SEGMENTS，您的桌子有多大？)

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

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

但这将是可怕的，可怕的缓慢。如果一个值在半个过程中发生变化，会发生什么呢？几乎可以肯定的是，一个SQL语句是最好的方法。

Answer 2

为什么不只是一个更新或合并？或者您可以使用游标处理数据来编写匿名pl/sql块，例如

declare
  cursor c is select * from aa for update;
begin
  for t_row in c loop
    update aa
     set val=t_row.val||' new value';
  end loop;
  commit;
end;

Answer 3

完全不更新它怎么样？

rename user_activity to user_activity_gmt

create view user_activity as
select id,
  user_id,
  event_date+10/24 as event_date,
  event,
  resource_id,
  resource_name
from user_activity_gmt;