在知识库中专注阅读，并随时返回相关工具与课程
大家好，这里是 Lucifer三思而后行，专注于提升数据库运维效率。 @TOC目录 首先，我们看一张图，我称之为 “四大皆空”，此图来源于 刘晨https://www.modb.pro/u/6583 的视频分享 Oracle中新增字段的点点滴滴https://www.modb.pro/video/5098，很有意思，从图中可以发现两段 SQL，看看一下这张图有什么奇怪的地方！ !https://i
大家好，这里是 Lucifer三思而后行，专注于提升数据库运维效率。
本章目标：掌握本章核心知识点 前置要求：完成前序章节学习 预计时长：60 分钟
首先，我们看一张图，我称之为 “四大皆空”，此图来源于 刘晨 的视频分享 Oracle中新增字段的点点滴滴，很有意思，从图中可以发现两段 SQL，看看一下这张图有什么奇怪的地方！
本文参考资料：
第一段 SQL：
1SQL> select * from test where c1 is null;2no rows selected34SQL> select * from test where c1 is not null;5 ID NAME C16------ ------- ---7 1 a看完第一段 SQL，是不是已经产生疑惑 😵？ 先不急，接着看第二段 SQL！
第二段 SQL：
1SQL> select dump(c1) as d from test;2D3-------4NULL56SQL> select nvl(c1,'is null') as c1 from test;7C18-------9IS NULL看完第二段 SQL，得出统一的结论就是： c1 字段值为空。
根据上面两段 SQL 的结论，也就有了上图中的 where PK dump/nvl？ 的疑问，那么到底是什么导致的这个问题呢？
首先，这明显不是一个正常的操作能够导致的问题，所以首先排除插入空值到非空字段的情况，需要从其他的思路的进行探讨。
通过 dbms_metadata.get_ddl 函数获取 test 表结构的定义：
1select dbms_metadata.get_ddl('TABLE', 'TEST') from dual;2DBMS_METADATA.GET_DDL('TABLE','TEST')3--------------------------------------------------------------------------------4CREATE TABLE "TEST"."TEST"5( "ID" NUMBER,6"NAME" VARCHAR2(8) DEFAULT 'a',7"C1" VARCHAR2(8) DEFAULT '' NOT NULL ENABLE8) SEGMENT CREATION IMMEDIATE9PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 25510NOCOMPRESS LOGGING11STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 214748364512PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 113BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)14TABLESPACE "USERS"小知识拓展：
可以发现，c1 字段是非空字段，且默认值为空。 为什么 Oracle 会允许空值插入到非空约束字段中？
想要搞明白原因，光靠猜测是没有用的，实践是检验真理的唯一标准。
有没有可能是，插入记录时有非空约束的列默认为空导致：
1SQL> insert into TEST (id, name) values (1, 'a');2insert into TEST (id, name) values (1, 'a')3*4ERROR at line 1:5ORA-01400: cannot insert NULL into ("TEST"."TEST"."C1")可以看到插入报错了，说明这个思路是错的，此路不通。
按理来说，Oracle 这么多版本的更新迭代之后，应该不会在 11G 版本还出现这种问题，综上所述，猜测可能是 11G 的新特性导致的 BUG。
查询官方文档中的 11G 新特性 Enhanced ADD COLUMN Functionality 可以发现：
在 11G 版本中，当添加带有默认值且非空约束的列时，不直接更新当前表的所有记录的该列默认值，而是将数据存储到数据字典中的 sys.col$ 表中，后续执行 DML 操作时会自动更新该列默认值。
接下来就是用新特性来测试一下，首先创建 TEST 表，不包含 C1 字段：
1CREATE TABLE TEST (ID NUMBER, NAME VARCHAR2(8) DEFAULT 'a');手动添加 c1 列（非空约束+默认值为空）：
1 alter table TEST add c1 varchar2(8) default '' not null;再次查询：
1SQL> select * from test where c1 is not null;2 ID NAME C13------ ------- ---4 1 a
破案了，函数是对的，c1 字段值在默认的情况下确实为空，NOT NULL 列的默认值为 NULL，如果不指定默认值那么就相当于默认值为 NULL。
上面通过猜测和实践得出了问题的原因，但还是有些不明所以：
.......通过 ”四大皆空“ 图看起来，使用 Where 条件返回了错误的数据，CBO 那么聪明，执行计划判断不出来？
第一个 SQL：
1SQL> select * from test where c1 is null;2no rows selected分析：当查询条件 c1 为空时，CBO 给出一个谓词 filter 过滤条件 NULL IS NOT NULL，这意味着查询条件恒假，当一个查询条件恒假的时候，Oracle 不需要真正执行语句，所以看到 Cost（%CPU） 为 0，所以当一个查询条件明显的违反表中的约束条件时，Oracle 并不会去执行这个查询语句，而是直接返回了 0 条记录。
第二个 SQL 的执行计划：
1SQL> select * from test where c1 is not null;2 ID NAME C13------ ------- ---4 1 a分析：当查询条件为 c1 不为空时，执行计划中并没有 filter 谓词条件，为什么呢？因为 c1 字段是非空约束，所以 CBO 判读 c1 is not null 这个查询条件是恒真的，也就不需要过滤，直接返回所有的数据。
结论： 简单的说，导致这个问题的原因是由于错误的数据存储于表中，而这导致了 CBO 在判断时出现了错误，导致和预期相反的结果返回，所以 where 并没有错误，是新特性的 BUG 导致 CBO 的判断错误。
Oracle 为什么要引入这个新特性？我们使用 3 种情况的分析一下！
准备测试数据：
1create table test(id number,name varchar2(1));2insert into test values(1,a);3insert into test values(2,b);4commit;5select * from test;通过 dump 操作来查看数据的实时情况：
1select dbms_rowid.rowid_relative_fno(rowid), dbms_rowid.rowid_block_number(rowid) from test;2alter system dump datafile 4 block 173109;
第一种情况：增加一个字段，不带默认值，不带非空约束
1alter table test add a1 varchar2(1);2desc test
当为表增加一个不带默认值，不带非空约束的字段时，已存记录的数据块中不会立刻存储该新增字段：
只有当更新字段或插入数据的时候，数据块中才会实际存储：
更新操作：
插入操作：
第二种情况：增加一个字段，带默认值，不带非空约束
📢 注意：针对这种情况，12C 引入了新特性：MetaData-Only DEFAULT Column Values for NULL Columns
1alter table test add a2 varchar2(1) default 'a';2desc testOracle 11g，新增一个带默认值，不带非空约束的字段，会立刻在表的数据块中增加该字段：
并执行全表更新的操作，将该值更新为默认值，DDL操作的执行时间和表的数据量相关：
第三种情况：增加一个字段，带默认值，带非空约束
📢 注意：针对这种情况，11G 引入了新特性：Enhanced ADD COLUMN Functionality！
1alter table test add a2 varchar2(1) default 'a' not null;2desc test
Oracle 11g，新增一个带默认值，带非空约束的字段，已存记录的数据块中不会立刻存储该新增字段：
而是将其作为元数据存储在数据字典中的 sys.col$ 中：
同时在 sys.ecol$ 中可以看到：
但是，当改变新增列的默认值时，sys.ecol$ 的数据不会实时变化，仅存储第一次增加列时的默认值：
只有当更新字段或插入数据的时候，数据块才会实际存储：
通过这种优化，缩短了DDL执行时间，这就是 Oracle 11G 引入 Enhanced ADD COLUMN Functionality 新特性的原因。
值得一提的是，Oracle 在 12C 以后已经修复了这个 BUG，增加了如下判断（DEFAULT 为 NULL 是禁止的）：
Oracle 学习路漫漫，茫茫文档需要看，直觉前路要变宽，到头还被 BUG 绊！😂
Oracle 一键巡检自动生成 Word 报告
Oracle 一键安装合集
Oracle一键安装脚本的 21 个疑问与解答
Oracle一键巡检脚本的 21 个疑问与解答
全网首发：Oracle 23ai 一键安装脚本（非 RPM）
Oracle 19C 最新 RU 补丁 19.24 ，一键安装！
Oracle Linux 7.9 一键安装 Oracle 19C
RedHat 9.4(aarch64) 一键安装 Oracle 19C
openEuler 22.03 LTS SP4 一键安装 Oracle 19C RAC
RHEL 7.9 一键安装 Oracle 19C 19.23 RAC
Oracle DataGuard GAP 修复手册
优化 Oracle：最佳实践与开发规范
DBA 必备：Linux 软件源配置全攻略
Linux 一键配置时钟同步全攻略
感谢您的阅读，这里是 Lucifer三思而后行，欢迎点赞+关注，我会持续分享数据库知识、运维技巧。