Oracle 查詢如何做到 “四大皆空“

作者：Lucifer 2022-03-26 10:13:58

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

問題與分析

首先，我們看一張圖，我稱之為 “四大皆空”，此圖來源于劉晨的視頻分享《Oracle中新增字段的點點滴滴》，很有意思，從圖中可以發現兩段 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 字段;
當查詢 c1 字段值為空時，沒有記錄返回，得出結論：test 表中不存在 c1 字段值為空的數據;
當查詢 c1 字段值不為空時，有一條記錄返回，且 c1 字段是空值，得出結論：test 表中存在 c1 字段值不能空，但是 c1 字段值返回是空值?

看完第一段 SQL，是不是已經產生疑惑 ? 先不急，接著看第二段 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;
當使用 DUMP 函數來判斷 c1 的值，返回值為空，根據官方文檔描述：If expr is null, then this function returns NULL，可以得出結論：c1 字段值為空。
當使用 NVL 函數來判斷 c1 的值，返回值為 IS NULL，根據官方文檔描述：If expr1 is null, then NVL returns expr2. If expr1 is not null, then NVL returns expr1 ，得出結論：c1 字段值為空。

看完第二段 SQL，得出統一的結論就是：c1 字段值為空。

根據上面兩段 SQL 的結論，也就有了上圖中的 where PK dump/nvl? 的疑問，那么到底是什么導致的這個問題呢?

猜測與實踐

首先，這明顯不是一個正常的操作能夠導致的問題，所以首先排除插入空值到非空字段的情況，需要從其他的思路的進行探討。

通過 dbms_metadata.get_ddl 函數獲取 test 表結構的定義：

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 數據庫中，對于 char 和 varchar2 字段來說，缺省值 '' 就是 null;
但是 where 條件后的 '' 不等于 null。

可以發現，c1 字段是非空字段，且默認值為空。為什么 Oracle 會允許空值插入到非空約束字段中?

想要搞明白原因，光靠猜測是沒有用的，實踐是檢驗真理的唯一標準。

猜測一

有沒有可能是，插入記錄時有非空約束的列默認為空導致：

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 這么多版本的更新迭代之后，應該不會在 11G 版本還出現這種問題，綜上所述，猜測可能是 11G 的新特性導致的 BUG。

查詢官方文檔中的 11G 新特性 Enhanced ADD COLUMN Functionality 可以發現：

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

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

手動添加 c1 列(非空約束+默認值為空)：

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

再次查詢：

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

破案了，函數是對的，c1 字段值在默認的情況下確實為空，NOT NULL 列的默認值為 NULL，如果不指定默認值那么就相當于默認值為 NULL。

深入與研究

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

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

Where 錯了嗎?

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

第一個 SQL：

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

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

第二個 SQL 的執行計劃：

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

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

結論：簡單的說，導致這個問題的原因是由于錯誤的數據存儲于表中，而這導致了 CBO 在判斷時出現了錯誤，導致和預期相反的結果返回，所以 where 并沒有錯誤，是新特性的 BUG 導致 CBO 的判斷錯誤。

新特性詳解

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

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

實踐演示

準備測試數據：

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 操作來查看數據的實時情況：

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

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

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

當為表增加一個不帶默認值，不帶非空約束的字段時，已存記錄的數據塊中不會立刻存儲該新增字段：

只有當更新字段或插入數據的時候，數據塊中才會實際存儲：

更新操作：

插入操作：

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

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

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

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

并執行全表更新的操作，將該值更新為默認值，DDL操作的執行時間和表的數據量相關：

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

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

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

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

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

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

但是，當改變新增列的默認值時，sys.ecol$ 的數據不會實時變化，僅存儲第一次增加列時的默認值：

只有當更新字段或插入數據的時候，數據塊才會實際存儲：

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

最后

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

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

本文參考資料：

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

責任編輯：武曉燕來源： Lucifer三思而后行