問(wèn)題與分析

首先，我們看一張圖，我稱之為 “四大皆空”，此圖來(lái)源于 劉晨 的視頻分享 《Oracle中新增字段的點(diǎn)點(diǎn)滴滴》，很有意思，從圖中可以發(fā)現(xiàn)兩段 SQL，看看一下這張圖有什么奇怪的地方!

SQL 分析

第一段 SQL：

SQL> select * from test where c1 is null;
no rows selected

SQL> select * from test where c1 is not null;
 ID NAME C1
------ ------- ---
 1 a

• 有一張 test 表有個(gè) c1 字段;
• 當(dāng)查詢 c1 字段值為空時(shí)，沒(méi)有記錄返回，得出結(jié)論：test 表中不存在 c1 字段值為空的數(shù)據(jù);
• 當(dāng)查詢 c1 字段值不為空時(shí)，有一條記錄返回，且 c1 字段是空值，得出結(jié)論：test 表中存在 c1 字段值不能空，但是 c1 字段值返回是空值?

看完第一段 SQL，是不是已經(jīng)產(chǎn)生疑惑 ? 先不急，接著看第二段 SQL!

第二段 SQL：

SQL> select dump(c1) as d from test;
D
-------
NULL

SQL> select nvl(c1,'is null') as c1 from test;
C1
-------
IS NULL

• 同一張 test 表的相同字段 c1;
• 當(dāng)使用 DUMP 函數(shù) 來(lái)判斷 c1 的值，返回值為空，根據(jù)官方文檔描述：If expr is null, then this function returns NULL，可以得出結(jié)論：c1 字段值為空。
• 當(dāng)使用 NVL 函數(shù) 來(lái)判斷 c1 的值，返回值為 IS NULL，根據(jù)官方文檔描述：If expr1 is null, then NVL returns expr2. If expr1 is not null, then NVL returns expr1 ，得出結(jié)論：c1 字段值為空。

看完第二段 SQL，得出統(tǒng)一的結(jié)論就是：c1 字段值為空。

根據(jù)上面兩段 SQL 的結(jié)論，也就有了上圖中的 where PK dump/nvl? 的疑問(wèn)，那么到底是什么導(dǎo)致的這個(gè)問(wèn)題呢?

猜測(cè)與實(shí)踐

首先，這明顯不是一個(gè)正常的操作能夠?qū)е碌膯?wèn)題，所以首先排除插入空值到非空字段的情況，需要從其他的思路的進(jìn)行探討。

通過(guò) dbms_metadata.get_ddl 函數(shù)獲取 test 表結(jié)構(gòu)的定義：

select dbms_metadata.get_ddl('TABLE', 'TEST') from dual;
DBMS_METADATA.GET_DDL('TABLE','TEST')
--------------------------------------------------------------------------------
CREATE TABLE "TEST"."TEST"
( "ID" NUMBER,
"NAME" VARCHAR2(8) DEFAULT 'a',
"C1" VARCHAR2(8) DEFAULT '' NOT NULL ENABLE
) SEGMENT CREATION IMMEDIATE
PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255
NOCOMPRESS LOGGING
STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1
BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
TABLESPACE "USERS"

小知識(shí)拓展：

• Oracle 數(shù)據(jù)庫(kù)中，對(duì)于 char 和 varchar2 字段來(lái)說(shuō)，缺省值 '' 就是 null;
• 但是 where 條件后的 '' 不等于 null。

可以發(fā)現(xiàn)，c1 字段是非空字段，且默認(rèn)值為空。 為什么 Oracle 會(huì)允許空值插入到非空約束字段中?

想要搞明白原因，光靠猜測(cè)是沒(méi)有用的，實(shí)踐是檢驗(yàn)真理的唯一標(biāo)準(zhǔn)。

猜測(cè)一

有沒(méi)有可能是，插入記錄時(shí)有非空約束的列默認(rèn)為空導(dǎo)致：

SQL> insert into TEST (id, name) values (1, 'a');
insert into TEST (id, name) values (1, 'a')
*
ERROR at line 1:
ORA-01400: cannot insert NULL into ("TEST"."TEST"."C1")

可以看到插入報(bào)錯(cuò)了，說(shuō)明這個(gè)思路是錯(cuò)的，此路不通。

猜測(cè)二

按理來(lái)說(shuō)，Oracle 這么多版本的更新迭代之后，應(yīng)該不會(huì)在 11G 版本還出現(xiàn)這種問(wèn)題，綜上所述，猜測(cè)可能是 11G 的新特性導(dǎo)致的 BUG。

查詢官方文檔中的 11G 新特性 Enhanced ADD COLUMN Functionality 可以發(fā)現(xiàn)：

在 11G 版本中，當(dāng)添加帶有默認(rèn)值且非空約束的列時(shí)，不直接更新當(dāng)前表的所有記錄的該列默認(rèn)值，而是將數(shù)據(jù)存儲(chǔ)到數(shù)據(jù)字典中的 sys.col$ 表中，后續(xù)執(zhí)行 DML 操作時(shí)會(huì)自動(dòng)更新該列默認(rèn)值。

接下來(lái)就是用新特性來(lái)測(cè)試一下，首先創(chuàng)建 TEST 表，不包含 C1 字段：

CREATE TABLE TEST (ID NUMBER, NAME VARCHAR2(8) DEFAULT 'a');  

手動(dòng)添加 c1 列(非空約束+默認(rèn)值為空)：

alter table TEST add c1 varchar2(8) default '' not null;

再次查詢：

SQL> select * from test where c1 is not null;
 ID NAME C1
------ ------- ---
 1 a

破案了，函數(shù)是對(duì)的，c1 字段值在默認(rèn)的情況下確實(shí)為空，NOT NULL 列的默認(rèn)值為 NULL，如果不指定默認(rèn)值那么就相當(dāng)于默認(rèn)值為 NULL。

深入與研究

上面通過(guò)猜測(cè)和實(shí)踐得出了問(wèn)題的原因，但還是有些不明所以：

• where PK dump/nvl?函數(shù)的結(jié)果是對(duì)的，where 真的錯(cuò)了嗎?
• 為什么要引入 Enhanced ADD COLUMN Functionality 新特性?
• .......

Where 錯(cuò)了嗎?

通過(guò) ”四大皆空“ 圖看起來(lái)，使用 Where 條件返回了錯(cuò)誤的數(shù)據(jù)，CBO 那么聰明，執(zhí)行計(jì)劃判斷不出來(lái)?

第一個(gè) SQL：

SQL> select * from test where c1 is null;
no rows selected

分析：當(dāng)查詢條件 c1 為空時(shí)，CBO 給出一個(gè)謂詞 filter 過(guò)濾條件 NULL IS NOT NULL，這意味著查詢條件恒假，當(dāng)一個(gè)查詢條件恒假的時(shí)候，Oracle 不需要真正執(zhí)行語(yǔ)句，所以看到 Cost(%CPU) 為 0，所以當(dāng)一個(gè)查詢條件明顯的違反表中的約束條件時(shí)，Oracle 并不會(huì)去執(zhí)行這個(gè)查詢語(yǔ)句，而是直接返回了 0 條記錄。

第二個(gè) SQL 的執(zhí)行計(jì)劃：

SQL> select * from test where c1 is not null;
 ID NAME C1
------ ------- ---
 1 a

分析：當(dāng)查詢條件為 c1 不為空時(shí)，執(zhí)行計(jì)劃中并沒(méi)有 filter 謂詞條件，為什么呢?因?yàn)?c1 字段是非空約束，所以 CBO 判讀 c1 is not null 這個(gè)查詢條件是恒真的，也就不需要過(guò)濾，直接返回所有的數(shù)據(jù)。

結(jié)論： 簡(jiǎn)單的說(shuō)，導(dǎo)致這個(gè)問(wèn)題的原因是由于錯(cuò)誤的數(shù)據(jù)存儲(chǔ)于表中，而這導(dǎo)致了 CBO 在判斷時(shí)出現(xiàn)了錯(cuò)誤，導(dǎo)致和預(yù)期相反的結(jié)果返回，所以 where 并沒(méi)有錯(cuò)誤，是新特性的 BUG 導(dǎo)致 CBO 的判斷錯(cuò)誤。

新特性詳解

Oracle 為什么要引入這個(gè)新特性?我們使用 3 種情況的分析一下!

• 在 Oracle 11G 之前，向現(xiàn)有表添加一個(gè)新列需要修改該表中的所有行，以添加新列。
• Oracle 11G 引入了元數(shù)據(jù)唯一默認(rèn)值的概念。將默認(rèn)子句添加到現(xiàn)有表的非空列，只涉及元數(shù)據(jù)更改，而不是對(duì)表中的所有行進(jìn)行更改。優(yōu)化器重寫新列的查詢，以確保結(jié)果與默認(rèn)定義一致。
• Oracle 12C 則更進(jìn)一步，允許元數(shù)據(jù)默認(rèn)值的強(qiáng)制和可選列。因此，在現(xiàn)有表中添加帶有默認(rèn)子句的新列將被作為一個(gè)元數(shù)據(jù)來(lái)處理，而不管該列是否被定義為不為空。這代表了空間保存和性能改進(jìn)。

實(shí)踐演示

準(zhǔn)備測(cè)試數(shù)據(jù)：

create table test(id number,name varchar2(1));
insert into test values(1,a);
insert into test values(2,b);
commit;
select * from test;

通過(guò) dump 操作來(lái)查看數(shù)據(jù)的實(shí)時(shí)情況：

select dbms_rowid.rowid_relative_fno(rowid), dbms_rowid.rowid_block_number(rowid) from test;
alter system dump datafile 4 block 173109;

第一種情況：增加一個(gè)字段，不帶默認(rèn)值，不帶非空約束：

alter table test add a1 varchar2(1);
desc test

當(dāng)為表增加一個(gè)不帶默認(rèn)值，不帶非空約束的字段時(shí)，已存記錄的數(shù)據(jù)塊中不會(huì)立刻存儲(chǔ)該新增字段：

只有當(dāng)更新字段或插入數(shù)據(jù)的時(shí)候，數(shù)據(jù)塊中才會(huì)實(shí)際存儲(chǔ)：

更新操作：

插入操作：

第二種情況：增加一個(gè)字段，帶默認(rèn)值，不帶非空約束

注意：針對(duì)這種情況，12C 引入了新特性：MetaData-Only DEFAULT Column Values for NULL Columns：

alter table test add a2 varchar2(1) default 'a';
desc test

Oracle 11g，新增一個(gè)帶默認(rèn)值，不帶非空約束的字段，會(huì)立刻在表的數(shù)據(jù)塊中增加該字段：

并執(zhí)行全表更新的操作，將該值更新為默認(rèn)值，DDL操作的執(zhí)行時(shí)間和表的數(shù)據(jù)量相關(guān)：

第三種情況：增加一個(gè)字段，帶默認(rèn)值，帶非空約束

注意：針對(duì)這種情況，11G 引入了新特性：Enhanced ADD COLUMN Functionality!

alter table test add a2 varchar2(1) default 'a' not null;
desc test

Oracle 11g，新增一個(gè)帶默認(rèn)值，帶非空約束的字段，已存記錄的數(shù)據(jù)塊中不會(huì)立刻存儲(chǔ)該新增字段：

而是將其作為元數(shù)據(jù)存儲(chǔ)在數(shù)據(jù)字典中的 sys.col$ 中：

同時(shí)在 sys.ecol$ 中可以看到：

但是，當(dāng)改變新增列的默認(rèn)值時(shí)，sys.ecol$ 的數(shù)據(jù)不會(huì)實(shí)時(shí)變化，僅存儲(chǔ)第一次增加列時(shí)的默認(rèn)值：

只有當(dāng)更新字段或插入數(shù)據(jù)的時(shí)候，數(shù)據(jù)塊才會(huì)實(shí)際存儲(chǔ)：

通過(guò)這種優(yōu)化，縮短了DDL執(zhí)行時(shí)間，這就是 Oracle 11G 引入 Enhanced ADD COLUMN Functionality 新特性的原因。

最后

值得一提的是，Oracle 在 12C 以后已經(jīng)修復(fù)了這個(gè) BUG，增加了如下判斷(DEFAULT 為 NULL 是禁止的)：

Oracle 學(xué)習(xí)路漫漫，茫茫文檔需要看，直覺(jué)前路要變寬，到頭還被 BUG 絆!

本文參考資料：

• 《非空字段空值對(duì)查詢的影響》http://yangtingkun.net/?p=1481 -- 楊廷琨
• 《非空字段空值的產(chǎn)生》http://yangtingkun.net/?p=1483 -- 楊廷琨
• 《Oracle中新增字段的點(diǎn)點(diǎn)滴滴》https://www.modb.pro/video/5098 -- 劉晨