更新x设置y = null需要很长时间

Question

在工作中，我有一张大桌子（大约300万行，例如40-50列）。有时我需要清空一些列，并用新数据填充它们。我没想到的是

UPDATE table1 SET y = null

与用例如从同一表的其他列在sql查询中生成的数据或从子查询中的其他表查询生成的数据填充该列相比，花费的时间要多得多。不管我一次遍历所有表行（如上面的更新查询中），还是我使用光标逐行遍历表（使用pk）都没有关系。无论是在工作中使用大型表还是创建小型测试表并将其填充成千上万的html" target="_blank">测试行都没有关系。将列设置为null总是比用一些动态数据更新列（每行都不同）花费的时间更长（在整个测试中，我遇到的因素是2到10）。

是什么原因呢？将列设置为null时，Oracle会做什么？或者-我的推理错误是什么？

谢谢你的帮助！

PS：我使用的是oracle 11g2，并且使用plsql developer和oracle sql developer都发现了这些结果。

Answer 1

概括

我认为更新为null的速度较慢，因为Oracle（错误地）试图利用它存储null的方式，导致它频繁地重新组织块中的行（“堆块压缩”），从而创建了许多额外的UNDO和重做。

null有什么特别之处？

从Oracle数据库概念开始：

“如果空值位于带有数据值的列之间，则它们存储在数据库中。在这种情况下，它们需要1个字节来存储列的长度（零）。

行尾的null不需要存储，因为新的行标题表示前一行中的其余列为null。例如，如果表的最后三列为空，则不会为这些列存储任何信息。在具有许多列的表中，应该最后定义更可能包含空值的列，以节省磁盘空间。”

测试

对更新进行基准测试非常困难，因为不能仅通过update语句来衡量更新的真实成本。例如，日志切换不会在每次更新时发生，而延迟的块清除将在以后发生。为了准确地测试更新，应该有多个运行，应该为每个运行重新创建对象，并且应该丢弃较高和较低的值。

为简单起见，下面的脚本不会抛出高低的结果，而只会测试具有单个列的表。但是，无论列数，其数据以及更新哪一列，仍然会出现问题。

我使用了http://www.oracle-developer.net/utilities.php中的RunStats实用程序来比较“更新为一个值”和“更新为一个空”的资源消耗。

create table test1(col1 number);

BEGIN
    dbms_output.enable(1000000);

   runstats_pkg.rs_start;

    for i in 1 .. 10 loop
        execute immediate 'drop table test1 purge';
        execute immediate 'create table test1 (col1 number)';
        execute immediate 'insert /*+ append */ into test1 select 1 col1
            from dual connect by level <= 100000';
        commit;
        execute immediate 'update test1 set col1 = 1';
        commit;
    end loop;

   runstats_pkg.rs_pause;
   runstats_pkg.rs_resume;

    for i in 1 .. 10 loop
        execute immediate 'drop table test1 purge';
        execute immediate 'create table test1 (col1 number)';
        execute immediate 'insert /*+ append */ into test1 select 1 col1
            from dual connect by level <= 100000';
        commit;
        execute immediate 'update test1 set col1 = null';
        commit;
    end loop;

   runstats_pkg.rs_stop();
END;
/

结果

有数十种差异，以下是我认为最相关的四种：

Type  Name                                 Run1         Run2         Diff
----- ---------------------------- ------------ ------------ ------------
TIMER elapsed time (hsecs)                1,269        4,738        3,469
STAT  heap block compress                     1        2,028        2,027
STAT  undo change vector size        55,855,008  181,387,456  125,532,448
STAT  redo size                     133,260,596  581,641,084  448,380,488

解决方案？

我能想到的唯一可能的解决方案是启用表压缩。压缩表不会发生尾随空存储的窍门。因此，即使Run2的“堆块压缩”数从2028年到23208甚至更高，我猜它实际上也无能为力。启用表压缩后，两次运行之间的重做，撤消和经过时间几乎相同。

但是，表压缩有很多潜在的缺点。更新为null将运行得快得多，但其他所有更新运行起来至少会稍慢一些。