代码之家 › 专栏 › 技术社区 › Cyntech

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

oracle10g oracle sql

Cyntech · 技术社区 · 15 年前

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

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

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

从总计约6000万行中选择50000行的块。我将它们拆分,因为a)Java(什么是更新进程被写入)内存不足,行数太多(我有每行的bean对象),B)只有4个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;

但是,撤消空间不足。我的印象是,如果提交是在每次更新之后进行的,那么对撤消空间的需求是最小的。我的假设是错误的吗?

3 回复 | 直到 15 年前

Jon Heller TenG 15 年前

一次更新可能不会遇到同样的问题,而且可能会更快地达到数量级。由于排序的原因,只需要大量的临时表空间。不过,如果DBA对临时表空间非常吝啬,那么最终可能会耗尽撤消空间或其他内容。(看看所有的部分,你的桌子有多大?)

但是如果你真的必须使用这个方法,也许你可以使用一个过滤器而不是order by。创建1200个桶,一次处理一个:

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

但这将是可怕的,可怕的缓慢。如果一个值在过程的中途改变了会发生什么?一条SQL语句几乎肯定是实现这一点的最佳方法。

Sayan Malakshinov 15 年前

为什么不只更新或合并一次? 或者,您可以用光标编写带有处理数据的匿名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;

Tony Andrews 15 年前

不更新怎么样?

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;