更新一个非常大的 oracle 表 [英] updating a very large oracle table

问题描述

我有一个非常大的表 people，有 60M 行索引在 id 上，希望基于查找表 id_conversion(1M 行)为每条记录填充一个字段 newid，其中包含 id 和 newid，索引在 id 上.当我跑步时

I have a very large table people with 60M rows indexed on id, wish to populate a field newid for every record based on a look up table id_conversion (1M rows) which contains id and newid, indexed on id. when I run

update people p set p.newid=(select l.newid from id_conversion l where l.id=p.id)

它运行了一个小时左右，然后我收到一个存档错误或 00257.对于分节运行更新或更好的 sql 命令有什么建议吗?

it runs for an hour or so and then I get an archive error ora 00257. Any suggestions for either running update in sections or better sql command?

推荐答案

如果您的 update 语句命中表的每一行，为了避免写入 Oracle 的撤消日志，那么您可能最好运行一个create table as select 查询将绕过所有撤消日志，这可能是您遇到的问题，因为它记录了 6000 万行的影响.然后，您可以删除旧表并将新表重命名为旧表的名称.

To avoid writing to Oracle's undo log if your update statement hits every single row of the table then you are likely better off running a create table as select query which will bypass all undo logs, which is likely the issue you're running into as it is logging the impact across 60 million rows. You can then drop the old table and rename the new table to that of the old table's name.

类似于:

create table new_people as
select l.newid,
       p.col2,
       p.col3,
       p.col4,
       p.col5
  from people p
  join id_conversion l
    on p.id = l.id;

drop table people;

-- rebuild any constraints and indexes
-- from old people table to new people table

alter table new_people rename to people;

作为参考，请阅读此处的一些提示:http://www.dba-oracle.com/t_efficient_update_sql_dml_tips.htm

For reference, read some of the tips here: http://www.dba-oracle.com/t_efficient_update_sql_dml_tips.htm

如果您基本上是在创建一个新表，而不仅仅是更新表的某些行，那么它可能会证明是更快的方法.

If you are basically creating a new table and not just updating some of the rows of a table it will likely prove the faster method.

这篇关于更新一个非常大的 oracle 表的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！