UPDATE table1 SET y = null
比使用生成的数据(例如,来自同一表的其他列的SQL查询或子查询中的其他表查询的数据)填充列所花费的时间更多.如果我一次通过所有表行(如上面的更新查询),或者如果我使用游标逐行地遍历表(不需要使用pk),这并不重要.如果我在工作中使用大表,或者如果我创建一个小的测试表并填充了大量的测试行,这并不重要.将列设置为null始终会更长(在整个测试中,我遇到的因素是2到10),而不是使用一些动态数据(每行不同)更新列.
这是什么原因？将列设置为null时,Oracle会做什么？或者 – 我的推理错误是什么？
谢谢你的帮助！
解决方法
我认为更新为null更慢,因为Oracle(不正确地)尝试利用它存储空值的方式,导致它频繁地重新组织块中的行(“堆块压缩”),创建了大量额外的UNDO和重做.
null有什么特别之处？
如果空值落在数据值列之间,那么空值将被存储在数据库中,在这些情况下,它们需要1个字节来存储列的长度(零).
一行中的尾随空值不需要存储,因为新的行标题表示上一行中的剩余列为空.例如,如果表的最后三列为空,则不会为这些列存储任何信息.在许多列的表中,
应该最后定义更容易包含空值的列,以节省磁盘空间.
测试
基准更新非常困难,因为更新的真实成本不能仅仅从更新语句来测量.例如,日志切换将会
每次更新都不会发生,延迟块清理将在以后发生.要准确测试更新,应该有多个运行,
应该为每个运行重新创建对象,并且应该丢弃高和低的值.
为了简单起见,下面的脚本不会抛出高低的结果,只能用单列测试表.但问题仍然存在,无论列数,数据和哪些列被更新.
我使用http://www.oracle-developer.net/utilities.php中的RunStats实用程序来比较update-to-a-value与update-to-a-null的资源消耗.
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
解决方案？
我可以想到的唯一可能的解决方案是启用表压缩.压缩表不会发生尾随空存储技巧.
所以即使“堆块压缩”数量从2028到23208的Run2更高,我猜这实际上并没有做任何事情.
两次运行之间的重做,撤消和已用时间与启用的表压缩几乎相同.
然而,表压缩有很多潜在的缺点.更新到null将运行得更快,但每个其他更新将运行至少稍慢一些.
