问题与分析首先我们来看一张图，我称之为“四全为空”。本图来自刘晨的视频分享《Oracle中新增字段的点点滴滴》，很有意思。找到两段SQL，来看看这张图有什么奇怪的吧！SQL第一段SQL分析：SQL>select*fromtestwherec1isnull;没有选择行SQL>select*fromtestwherec1isnotnull;IDNAMEC1---------------1a有一个test表，有一个c1字段；当查询c1字段值为空时，没有返回任何记录，断定test表中没有记录有c1字段值为空的数据；当查询c1字段值不为空时，返回一条记录，c1字段为空，断定test表中有c1字段值不能为空，但返回的c1字段值是它是一个空值？看完第一段SQL，是不是已经有了疑惑？不着急，接着看第二段SQL！第二段SQL：SQL>selectdump(c1)asdfromtest;D----NULLSQL>selectnvl(c1,'isnull')asc1fromtest;C1------ISNULL同一个测试表的同一个字段c1；使用DUMP函数判断c1的值时，返回值为空。根据官方文档描述：如果expr为null，那么这个函数返回NULL，可以断定c1字段的值为空。使用NVL函数判断c1的值时，返回值为ISNULL。根据官方文档描述：如果expr1为null，则NVL返回expr2。如果expr1不为null，则NVL返回expr1，断定c1字段的值为null。看了第二段SQL，统一的结论是c1字段的值为空。根据上面两段SQL的结论，还有一个问题就是PKdump/nvl在哪里？上图中，那么是什么导致了这个问题呢？猜测与实践首先，这显然不是正常操作问题导致的，所以首先排除向非空字段插入空值的情况，需要从其他思路来讨论。通过dbms_metadata.get_ddl函数获取测试表结构定义：selectdbms_metadata.get_ddl('TABLE','TEST')fromdual;DBMS_METADATA.GET_DDL('TABLE','TEST')----------------------------------------------------------------------------创建表“测试”。“测试”（“ID”号码，“名称”VARCHAR2（8）默认'a'，"C1"VARCHAR2(8)DEFAULT''NOTNULLENABLE)SEGMENTCREATIONIMMEDIATEPCTFREE10PCTUSED40INITRANS1MAXTRANS255NOCOMPRESSLOGGINGSTORAGE(INITIAL65536NEXT1048576MINEXTENTS1MAXEXTENTS2147483645PCTINCREASE0FREELISTS1FREELISTGROUPS1BUFFER_POOLDEFAULTFLASH_CACHEDEFAULTCELL_FLASH_CACHEDEFAULT)TABLESPACE"USERS"小知识拓展：在Oracle数据库中，对于char和varchar2字段，默认值''为null；但where条件后的''不等于null。可以发现c1字段是一个非空字段，默认值为空。为什么Oracle允许在非空约束字段中插入空值？靠猜测来理解原因是没有用的。实践是检验真理的唯一标准。猜测是否有可能插入记录时非空约束的列默认为空：SQL>insertintoTEST(id,name)values(1,'a');insertintoTEST(id,name)values(1,'a')*ERRORatline1:ORA-01400:cannotinsertNULLinto("TEST"."TEST"."C1")可以看到insert报错，说明这个思路是错误的，这条路被堵住了。猜想2按理说，Oracle经过这么多版本的更新迭代，11G版本应该不会出现这个问题。综上，猜测可能是11G的新特性导致的BUG。查询官方文档中的11G新特性EnhancedADDCOLUMNFunctionality，可以发现：在11G版本中，添加具有默认值和非空约束的列时，不会直接更新该列的默认值对于当前表中的所有记录，但数据都存储在数据字典中的sys.col$表中，后续进行DML操作时会自动更新该列的默认值。下一步是使用新功能对其进行测试。首先创建一个不包含C1字段的TEST表：CREATETABLETEST(IDNUMBER,NAMEVARCHAR2(8)DEFAULT'a');手动添加c1列（非空约束+默认值为空）：altertableTESTaddc1varchar2(8)default''notnull;再次查询：SQL>select*fromtestwherec1isnotnull;IDNAMEC1---------------1a解决了case，功能正确，c1字段的值确实默认为空，并且默认值为NOTNULL列为NULL。如果不指定默认值，则相当于默认值NULL。深入研究，通过猜测和实践，已经得出了问题的原因，但还是有些不明朗。所以：PKdump/nvl在哪里？函数的结果是正确的，到底哪里错了？为什么要引入EnhancedADDCOLUMNFunctionality这个新特性？......哪里错了？从“四个都为空”的图来看，好像是使用Where条件返回了错误的数据。CBO聪明到无法判断执行计划？第一条SQL：SQL>select*fromtestwherec1isnull;norowsselected分析：当查询条件c1为空时，CBO给出一个predicatefilter过滤条件NULLISNOTNULL，表示查询条件永远为假。当一个查询条件一直为false时，Oracle是没有必要真正执行语句的，所以我们看到Cost(%CPU)为0，所以当一个查询条件明显违反了表中的约束时，Oracle是不会执行查询的语句，而是直接返回0条记录。第二条SQL的执行计划：SQL>select*fromtestwherec1isnotnull;IDNAMEC1---------------1a分析：当查询条件为c1不为空时，执行计划中没有过滤谓词条件。为什么？因为c1字段是非空约束，CBO解释查询条件c1不为空永远为真，所以不需要过滤，直接返回所有数据。结论：简单的说，这个问题的原因是表中存储了错误的数据，导致CBO的判断错误，导致返回相反的结果，所以并没有哪里出错，是新特性的BUG导致CBO做出了错误的判断。新特性详情Oracle为什么要引入这个新特性？下面就用3种情况来分析吧！在Oracle11G之前，向现有表添加新列需要修改表中的所有行以添加新列。Oracle11G引入了元数据唯一默认值的概念。向现有表的非空列添加默认子句仅涉及元数据更改，而不是对表中所有行的更改。优化器重写新列的查询以确保结果与默认定义一致。Oracle12C更进一步，允许元数据默认值的强制和可选列。因此，将带有默认子句的新列添加到现有表中将作为一条元数据处理，而不管该列是否定义为非空。这代表了空间节约和性能改进。练习demo准备测试数据：createtabletest(idnumber,namevarchar2(1));insertintotestvalues(1,a);insertintotestvalues(2,b);commit;select*fromtest;bydumpoperation查看数据实时情况：selectdbms_rowid.rowid_relative_fno(rowid),dbms_rowid.rowid_block_number(rowid)fromtest;改变系统转储数据文件4块173109；第一种情况：增加一个字段，没有默认值，没有非空约束：altertabletestadda1varchar2(1);desctest当向表中添加一个没有默认值且没有非空约束的字段时，新字段不会立即存储到已有记录的数据块中：只有在更新字段或插入数据时，才会实际数据块中存储：更新操作：插入操作：第二种情况：添加一个有默认值且没有非空约束的字段注：针对这种情况，12C引入了一个新特性：MetaData-OnlyDEFAULTColumnValues对于NULL列：altertabletestadda2varchar2(1)default'a';desctestOracle11g，新增一个默认值无非空约束的字段，会在表的数据块中添加这个字段：并执行更新全表的操作，将值更新为默认值，DDL操作的执行时间与表的数据量有关：第三种情况：添加一个有默认值的字段，Withnon-nullconstraint注意：针对这种情况，11G引入了一个新特性：Enhanced添加列功能！altertabletestadda2varchar2(1)default'a'notnull;desctestOracle11g，新增一个带默认值的，和对于非空约束的字段，新字段不会立即存入数据块中现有记录：相反，它将作为元数据存储在数据字典中的sys.col$中：同时，您可以在sys.ecol$中看到它To：但是，当更改新添加的列的默认值时,sys.ecol$的值数据不会实时变化，只存储第一次添加列时的默认值：只有更新字段或插入数据时，才会真正存储数据块：通过这种优化，DDL执行时间缩短，这是Oracle11G引入EnhancedADDCOLUMNFunctionality新特性的原因最后，值得一提的是，Oracle在12C之后修复了这个bug，增加了如下判断（默认为禁止NULL）：前方需要加宽，最后被BUG绊倒！本文参考资料：《非空字段空值对查询的影响》http://yangtingkun.net/?p=1481--杨廷坤《非空字段空值的产生》http://yangtingkun.net/?p=1483--杨廷坤《Oracle中新增字段的点点滴滴》https://www.modb.pro/video/5098--刘晨
