如何批量更新oracle pl/sql中的大表以避免用完undospace？

Question

我有一张非常大的桌子（5毫米记录）。我试图用随机字母数字来混淆表中每条记录的 VARCHAR2 列。我的过程在较小的数据集上成功执行，但它最终将在我无法控制其设置的远程数据库上使用，因此我想批量执行 UPDATE 语句以避免用完撤消空间。

是否有某种我可以启用的选项，或者是批量更新的标准方法？

我要补充的是，没有被混淆的记录不会有任何显着特征，因此我在循环中使用 rownum 的想法行不通（我认为）。

Answer 1

如果要更新表中的每一行，最好执行“创建表作为选择”，然后删除/截断原始表并重新附加新数据。如果您有分区选项，则可以将新表创建为具有单个分区的表，然后只需将其与 EXCHANGE PARTITION 交换即可。

插入需要更少的撤消，并且不带日志记录的直接路径插入（/+APPEND/ 提示）也不会生成太多重做。

无论采用哪种机制，都可能仍然存在旧值的“取证”证据（例如，保留在撤消中或由于行移动而分配给表的“可用”空间中）。

Answer 2

以下内容未经测试，但应该可以工作：

declare
  l_fetchsize number := 10000;
  cursor cur_getrows is
  select rowid, random_function(my_column)
    from my_table;

  type rowid_tbl_type      is table of urowid;
  type my_column_tbl_type  is table of my_table.my_column%type;

  rowid_tbl     rowid_tbl_type;
  my_column_tbl my_column_tbl_type;
begin

  open cur_getrows;
  loop
    fetch cur_getrows bulk collect  
      into rowid_tbl, my_column_tbl 
      limit l_fetchsize;
    exit when rowid_tbl.count = 0;

    forall i in rowid_tbl.first..rowid_tbl.last
      update my_table 
         set my_column = my_column_tbl(i)
       where rowid     = rowid_tbl(i);
    commit;
  end loop;
  close cur_getrows;
end;
/

这并不是最有效的——单个更新就可以了——但它会使用 ROWID 执行更小的、用户可调的批次。

Answer 3

为此，我将主键映射到整数 (mod n)，然后对每个 x 执行更新，其中 0 <= x <名词

例如，也许你不走运，主键是一个字符串。您可以使用您最喜欢的散列函数对其进行散列，并将其分成三个分区：

UPDATE myTable SET a=doMyUpdate(a) WHERE MOD(ORA_HASH(ID), 3)=0
UPDATE myTable SET a=doMyUpdate(a) WHERE MOD(ORA_HASH(ID), 3)=1
UPDATE myTable SET a=doMyUpdate(a) WHERE MOD(ORA_HASH(ID), 3)=2

您可能有更多分区，并且可能希望将其放入循环中（进行一些提交）。

Answer 4

如果我必须更新数百万条记录，我可能会选择不更新。

我更有可能创建一个临时表，然后从旧表插入数据，因为插入不占用空间大量的重做空间并且需要更少的撤消。

CREATE TABLE new_table as select <do the update "here"> from old_table;

index new_table
grant on new table
add constraints on new_table
etc on new_table

drop table old_table
rename new_table to old_table;

您可以使用并行查询来做到这一点，大多数操作上不记录日志会产生非常多的结果
很少重做，根本不需要撤消——只需要更新数据所需时间的一小部分
数据。