問題與分析

首先，我們看一張圖，我稱之為 “四大皆空”，此圖來源于 劉晨 的視頻分享 《Oracle中新增字段的點點滴滴》，很有意思，從圖中可以發(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 表有個 c1 字段;
• 當(dāng)查詢 c1 字段值為空時，沒有記錄返回，得出結(jié)論：test 表中不存在 c1 字段值為空的數(shù)據(jù);
• 當(dāng)查詢 c1 字段值不為空時，有一條記錄返回，且 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ù) 來判斷 c1 的值，返回值為空，根據(jù)官方文檔描述：If expr is null, then this function returns NULL，可以得出結(jié)論：c1 字段值為空。
• 當(dāng)使用 NVL 函數(shù) 來判斷 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? 的疑問，那么到底是什么導(dǎo)致的這個問題呢?

猜測與實踐

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

通過 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"

小知識拓展：

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

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

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

猜測一

有沒有可能是，插入記錄時有非空約束的列默認(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")

可以看到插入報錯了，說明這個思路是錯的，此路不通。

猜測二

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

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

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

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

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

手動添加 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ù)是對的，c1 字段值在默認(rèn)的情況下確實為空，NOT NULL 列的默認(rèn)值為 NULL，如果不指定默認(rèn)值那么就相當(dāng)于默認(rèn)值為 NULL。

深入與研究

上面通過猜測和實踐得出了問題的原因，但還是有些不明所以：

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

Where 錯了嗎?

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

第一個 SQL：

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

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

第二個 SQL 的執(zhí)行計劃：

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

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

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

新特性詳解

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

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

實踐演示

準(zhǔn)備測試數(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;

通過 dump 操作來查看數(shù)據(jù)的實時情況：

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

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

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

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

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

更新操作：

插入操作：

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

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

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

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

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

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

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

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

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

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

同時在 sys.ecol$ 中可以看到：

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

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

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

最后

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

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

本文參考資料：

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