NULL的官方定義
The PL/SQL data type BOOLEAN stores logical values, which are the Boolean values TRUE and FALSE and the value NULL. NULL represents an unknown value .
NULL值是關聯式資料庫系統布林型(TRUE、FALSE、NULL)中比較特殊型別的一種值，NULL代表未知的。

NULL的小結：
1、 NULL的判斷只能定性，而不能定值，即 NULL不可用於比較大小，所有與NULL的操作結果都是NULL
備註：簡單的說，由於NULL存在著無數的可能，因此兩個NULL值既不是相等的關係，又不是不相等的關係，不能比較兩個NULL的大小，因此，對 NULL 的=、!=、>、<、>=、<=、+ 、-、*、/等操作的結果都是未知的，所以這些操作的結果仍然是NULL

2、 NULL與ORDER BY
對欄位進行排序時，所有的 NULL都預設放在最後

3、 NULL與COUNT()
count(column)不包括對NULL的統計，count(*)包括對NULL的統計是因為oracle有一個偽列rowid不會為NULL
訣竅1、如果要查詢一個表的記錄數，可以透過全表掃描的方法，也可以透過COUNT非空列記錄數的方法，如果此時非空列上存在索引，就可以直接訪問索引獲得資料
訣竅2、如果要查詢一個可能為空欄位的非空記錄數，如果該列上建立了單列索引，直接訪問索引可以獲得資料

4、索引不儲存NULL值的記錄
索引是有序排列的，當一個空值進入索引時，因無法確定其在索引中的位置，所以oracle的索引不儲存索引列全為NULL值的記錄，掃描索引會漏掉索引列全為NULL值的記錄， oracle永遠把確保目標SQL結果的正確性放在第一位，遇到查詢NULL值的情況就只能走全表掃描
備註1：單列索引時，如果一條記錄中這個欄位為NULL，那麼索引不會儲存這條記錄
備註2：複合索引時，如果一條記錄中組成複合索引的所有欄位都是null時，那麼索引不會儲存這條記錄(如果某一個索引列不為空，那麼索引就會包括這條記錄，即使其他所有的列都是NULL值)

5、 NULL與唯一性索引的關係
5.1、單列具有唯一性索引時，該列可以插入多行null,因為Oracle不認為這些NULL是相等的
5.2、多列具有複合唯一性索引時，多列不能插入多行null和非null一樣組合的資料（比如兩列A，B，不可以插入多行1，null，但可以插入多行null，null）

6、 NULL與主鍵的關係
主鍵=NOT NULL + UNIQUE CONSTRAINT + UNIQUE INDEX
某列建立為主鍵時，原來該列的欄位屬性可以為NULL ，如果該列已經存在NULL值，主鍵無法建立，如果該列不存在NULL值，主鍵建立成功後，該列的欄位屬性自動變成NOT NULL

7、 NULL與新增not null欄位的理解
7.1、alter table t add col1(number)
不需要更新欄位值，執行時間比較短。
像上面增加列的操作時，會同時更新sys.ecol$和sys.col$資料字典表，若以後再修改這個預設值，則只是會修改sys.col$的值，且以後每次查詢也是從sys.col$的default$列獲取預設值，我們可以根據sys.eclo$、sys.col$和dba_objects查詢相關表和欄位資訊，
7.2、alter table t add col2(number) default 0;
需要更新欄位值，執行時間比較長。
如果新增一個含有預設值的欄位，那麼會立即更新每一行，在更新過程中，那麼執行時間會很久，取決於表中資料量的大小，會有一個EXCLUSIVE級別的鎖在該表上,期間會影響所有記錄的DML操作，可能會因UNDO不足對其他操作有影響。。
7.3、alter table t add col3(number) default 0 not null;
不需要更新欄位值，執行時間比較短。
預設值是儲存於資料字典表中的，並不是儲存在原表記錄上，即新增一個NOT NULL和預設值的欄位，以後每次需要使用該欄位時，預設值都是從資料字典中查詢到的，這樣就減少了新增欄位時的DDL語句時間，也減少了儲存空間(不用每條需要使用預設值的記錄都儲存預設值)。
所以：大表新增有預設值的欄位要特別小心，11g要使用 d efault XX not null
11g新特性，新增一個有預設值的NOT NULL約束的欄位，預設值不會像以前一樣，插入每條記錄中，而是會儲存於資料字典表
12C又更進一步了，即新增有預設值且允許為null時，也不會更新每一行

8、 NULL與索引\全表掃描的關係(IS NULL、IS NOT NULL的條件)
8.1、列是NULL屬性，且該列只有單列索引，直接select該列沒有任何where條件走全表掃描,即使hint中指定了走索引

select object_id from table1 --object_id允許為null，建有索引，一定全表掃描

select * from table1 where object_id=XX --object_id允許為null，建有索引，會走索引，因為XX不是null

8.1的解決之道：選擇合適的非空欄位建立組合索引 ,即組合列的組合資料不全為NULL會存放在索引中，這樣就直接查詢索引了
8.2、列只有單列索引，列不管是否NULL屬性，where條件引用該列採用 IS NULL則走全表掃描，不走索引，即使hint中指定了走索引
8.2的解決之道： IS NULL希望使用索引的情況，則需要結合查詢條件選擇合適的非空欄位建立組合索引 ,即組合列的組合資料不全為NULL會存放在索引中，這樣就會走索引掃描了
8.3、IS NOT NULL走索引的條件和普通走索引條件一樣，都是欄位內容大部分都是NULL，只有少部分是非NULL時
8.3的理解：如果在索引列上條件為IS NOT NULL，因為索引列的所有非空值都儲存在索引中，按道理也是可以走索引的。但是如果走索引，最佳化程式需要從索引中讀取每一個索引鍵值，再對映到表中的行。如果欄位小部分內容是NULL，大部分都是非NULL，那就是欄位的大部分資料都在索引上，大部分資料從索引獲取鍵值再對映到表上，那還不如直接全表掃描呢

9、向後引申：where條件走索引的前提
9.1、where條件囊括的資料鍵值在索引上能夠找到
9.2、where條件囊括的物理資料塊佔整表物理資料塊範圍比較小，也就是透過索引掃描比全表掃描快(為什麼是物理資料塊，而非資料行數,具體見CLUSTERING_FACTOR)

10 、官方文件可以佐證索引不儲存空塊的一段話如下
Fast full index scans are an alternative to a full table scan when the index contains all the columns that are needed for the query, and at least one column in the index key has the NOT NULL constraint

11 、子查詢有null值時，not in (子查詢)的結果是null，而in (子查詢 ) 的結果不會這樣
SELECT distinct emp.employee_id FROM employees emp order by 1;--107行，沒有null的行
SELECT distinct mgr.manager_id FROM employees mgr order by 1;--19行，最後一行是null
SELECT emp.employee_id,emp.last_name FROM employees emp WHERE emp.employee_id not IN (SELECT mgr.manager_id FROM employees mgr);--0行結果
SELECT emp.employee_id,emp.last_name FROM employees emp WHERE emp.employee_id not IN (SELECT mgr.manager_id FROM employees mgr where mgr.manager_id is not null);--89行結果
SELECT emp.employee_id,emp.last_name FROM employees emp WHERE emp.employee_id IN (SELECT mgr.manager_id FROM employees mgr);--18行結果
SELECT emp.employee_id,emp.last_name FROM employees emp WHERE emp.employee_id IN (SELECT mgr.manager_id FROM employees mgr where mgr.manager_id is not null);--18行結果

12 、where條件的欄位如果值有null，如果where後面還有not in子查詢，則會忽略外查詢中null的查詢結果
select * from employees where DEPARTMENT_ID not in (select DEPARTMENT_ID from departments);--沒有結果
select * from employees where nvl(DEPARTMENT_ID,1) not in (select DEPARTMENT_ID from departments);--有DEPARTMENT_ID為null的那一行
select * from employees where DEPARTMENT_ID in (select DEPARTMENT_ID from departments);--106行記錄
select * from employees where nvl(DEPARTMENT_ID,1) in (select DEPARTMENT_ID from departments)--106行記錄

select department_id from departments order by 1;--27行，沒有null
SELECT department_id FROM employees order by 1;--107行，最後一行是null
SELECT employee_id, last_name FROM employees e,departments d where e.department_id=d.department_id ORDER BY d.department_name;--結果為106行，employee_id=178這行的department_id欄位是null值，不能用於=比較
SELECT employee_id, last_name FROM employees e ORDER BY (SELECT department_name FROM departments d WHERE e.department_id = d.department_id);--結果為107行，employee_id=178這行的department_id欄位是null值，所以排最後

-- NULL不可用於比較
SQL> select count(*) from test1;
COUNT(*)
----------
25251
SQL> insert into TEST1(OWNER,OBJECT_NAME,OBJECT_ID,DATA_OBJECT_ID) values('test','test',null,1);
1 row created.
SQL> insert into TEST1(OWNER,OBJECT_NAME,OBJECT_ID,DATA_OBJECT_ID) values('test','test',null,2);
1 row created.
SQL> insert into TEST1(OWNER,OBJECT_NAME,OBJECT_ID,DATA_OBJECT_ID) values('test','test',null,3);
1 row created.
SQL> select * from test1 where OBJECT_ID>null;
no rows selected
SQL> select * from test1 where OBJECT_ID<null;
no rows selected
SQL> select * from test1 where OBJECT_ID=null;
no rows selected
SQL> select count(*) from test1 where OBJECT_ID is null;
COUNT(*)
----------
3

-- NULL值相關的操作同樣都為NULL
SQL> select OBJECT_ID from test1 where OBJECT_ID=200;
OBJECT_ID
----------
200
SQL> select OBJECT_ID+null from test1 where OBJECT_ID=200;
OBJECT_ID+NULL
--------------

-- NULL與ORDER BY
SQL> select * from hid;
HIDD HNAME
---------- --------------------
1
2
3
1
2
SQL> select * from hid order by 1;
HIDD HNAME
---------- --------------------
1
2
3
1
2
SQL> select * from hid order by 2;
HIDD HNAME
---------- --------------------
1
2
2
1
3
SQL>

-- NULL與COUNT()
SQL> select * from test_null;
HID HNAME
---------- --------------------
1 1
2 2

3
SQL> select count(*) from test_null;
COUNT(*)
----------
4
SQL> select count(hname) from test_null;
COUNT(HNAME)
------------
2
SQL> select count(hid) from test_null;
COUNT(HID)
----------
3
SQL> select count(rowid) from test_null;
COUNT(ROWID)
------------
4

-- NULL與唯一性索引的關係
SQL> create unique index ind_unique on hid(hidd,hname);
Index created.
SQL> insert into hid values(9,null);
1 row created.
SQL> insert into hid values(9,null);
insert into hid values(9,null)
*
ERROR at line 1:
ORA-00001: unique constraint (SYS.IND_UNIQUE) violated
SQL> insert into hid values(null,null);
1 row created.
SQL> insert into hid values(null,null);
1 row created.

-- NULL與索引\全表掃描的關係(IS NULL、IS NOT NULL的條件)
</null;
SQL> desc TEST1
Name Null? Type
----------------------------------------- -------- ----------------------------
OWNER VARCHAR2(30)
OBJECT_NAME VARCHAR2(128)
OBJECT_ID NUMBER
DATA_OBJECT_ID NOT NULL NUMBER
OBJECT_TYPE VARCHAR2(19)
STATUS VARCHAR2(7)
TEMPORARY VARCHAR2(1)
GENERATED VARCHAR2(1)
SECONDARY VARCHAR2(1)
NAMESPACE NUMBER
SQL> create index ind_O_ID on TEST1( OBJECT_ID );
SQL> create index ind_D_O_ID on TEST1( DATA_OBJECT_ID );
SQL> select count(*) from TEST1;
COUNT(*)
----------
25248
SQL> select count(*) from TEST1 where DATA_OBJECT_ID=OBJECT_ID ;
COUNT(*)
----------
25248
SQL> select * from test1 where OBJECT_ID is null;
no rows selected
SQL> set autotrace traceonly exp
SQL> set linesize 200
--- 直接查詢索引欄位，如果欄位屬性為null，則走全表掃描
SQL> select OBJECT_ID from test1;
Execution Plan
----------------------------------------------------------
Plan hash value: 4122059633
---------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 28775 | 365K| 60 (2)| 00:00:01 |
| 1 | TABLE ACCESS FULL | TEST1 | 28775 | 365K| 60 (2)| 00:00:01 |
---------------------------------------------------------------------------

SQL> select DATA_OBJECT_ID from test1;
Execution Plan
----------------------------------------------------------
Plan hash value: 3894717762
-----------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-----------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 28775 | 365K| 17 (0)| 00:00:01 |
| 1 | INDEX FAST FULL SCAN | IND_D_O_ID | 28775 | 365K| 17 (0)| 00:00:01 |
-----------------------------------------------------------------------------------

-- where條件是索引欄位，如果條件值不為null，則走索引
SQL> select * from test1 where object_id=99 ;
Execution Plan
----------------------------------------------------------
Plan hash value: 321031222
----------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 144 | 2 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID| TEST1 | 1 | 144 | 2 (0)| 00:00:01 |
|* 2 | INDEX RANGE SCAN | IND_O_ID | 1 | | 1 (0)| 00:00:01 |
----------------------------------------------------------------------------------------

SQL> select * from test1 where data_object_id=99;
Execution Plan
----------------------------------------------------------
Plan hash value: 3689194030
------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 144 | 2 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID| TEST1 | 1 | 144 | 2 (0)| 00:00:01 |
|* 2 | INDEX RANGE SCAN | IND_D_O_ID | 1 | | 1 (0)| 00:00:01 |
------------------------------------------------------------------------------------------

-- where條件是索引欄位，欄位只有單列索引，如果條件為is null，必走全表掃描
SQL> select * from test1 where object_id is null ;
Execution Plan
----------------------------------------------------------
Plan hash value: 4122059633
---------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 2 | 288 | 60 (2)| 00:00:01 |
|* 1 | TABLE ACCESS FULL | TEST1 | 2 | 288 | 60 (2)| 00:00:01 |
---------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("OBJECT_ID" IS NULL)

--where欄位是not null屬性，條件為is null，也走全表掃描？但是null和not null欄位的複合索引就會走索引？
原因是因為not null時，索引欄位永遠不存在null值，但是複合索引會出現一個索引建值一個欄位可能是null另一個欄位不是，即索引鍵值會有null
SQL> select * from test1 where data_object_id is null;
Execution Plan
----------------------------------------------------------
Plan hash value: 1588389598
----------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 144 | 0 (0)| |
|* 1 | FILTER | | | | | |
| 2 | TABLE ACCESS FULL| TEST1 | 28775 | 4046K| 60 (2)| 00:00:01 |
----------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter( NULL IS NOT NULL )

--- where條件是索引欄位，如果條件為is not null，為什麼沒走索引？
SQL> select * from test1 where OBJECT_ID is not null ;
Execution Plan
----------------------------------------------------------
Plan hash value: 4122059633
---------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 28775 | 4046K| 60 (2)| 00:00:01 |
|* 1 | TABLE ACCESS FULL| TEST1 | 28775 | 4046K| 60 (2)| 00:00:01 |
---------------------------------------------------------------------------

SQL> select * from test1 where DATA_OBJECT_ID is not null;
Execution Plan
----------------------------------------------------------
Plan hash value: 4122059633
---------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 28775 | 4046K| 60 (2)| 00:00:01 |
| 1 | TABLE ACCESS FULL| TEST1 | 28775 | 4046K| 60 (2)| 00:00:01 |
---------------------------------------------------------------------------
SQL>
解釋：如果在索引列上條件為Is Not Null ，因為索引列的所有非空值都儲存在索引中，按道理也是可以走索引的。但是本例中count是 25248， DATA_OBJECT_ID=OBJECT_ID的count也是25248，因為 DATA_OBJECT_ID是NOT NULL的，也就是說結果為NULL的count是0，也就是 IS NOT NULL 資料就是全表資料，如果走索引，最佳化程式需要從索引中讀取每一個索引鍵值，再對映到表中的行。整表的每一行都從索引獲取鍵值再對映到表上，那還不如直接全表掃描呢

-- is not null是會走索引的
SQL> create table test2 as select * from test1;
Table created.
SQL> update test2 set OWNER=null;
25251 rows updated.
SQL> select count(*) from test2 where OWNER is not null;
COUNT(*)
----------
0
SQL> select count(*) from test2 where OWNER is null;
COUNT(*)
----------
25251
SQL> update test2 set OWNER='good' where rownum<50;
49 rows updated.
SQL> create index idx_owner2 on test2(owner);
Index created.
SQL> exec dbms_stats.gather_table_stats('SYS','TEST2',cascade=>true);
PL/SQL procedure successfully completed.
SQL> set autotrace traceonly exp
SQL> select * from TEST2 where owner is not null;
Execution Plan
----------------------------------------------------------
Plan hash value: 411567793
------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 49 | 2548 | 2 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID| TEST2 | 49 | 2548 | 2 (0)| 00:00:01 |
|* 2 | INDEX FULL SCAN | IDX_OWNER2 | 49 | | 1 (0)| 00:00:01 |
------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - filter("OWNER" IS NOT NULL)

-- 建立複合索引後is null走索引的，直接查詢該索引欄位也走索引
SQL> create index ind_fuhe on test1(OBJECT_ID,DATA_OBJECT_ID);
Index created.
SQL> set linesize 200
SQL> set autotrace traceonly exp
SQL> select * from test1 where object_id is null;
Execution Plan
----------------------------------------------------------
Plan hash value: 2734972870
----------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 3 | 168 | 3 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID| TEST1 | 3 | 168 | 3 (0)| 00:00:01 |
|* 2 | INDEX RANGE SCAN | IND_FUHE | 3 | | 2 (0)| 00:00:01 |
----------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("OBJECT_ID" IS NULL)

SQL> select object_id from test1;
Execution Plan
----------------------------------------------------------
Plan hash value: 3653525288
---------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 25251 | 123K| 22 (0)| 00:00:01 |
| 1 | INDEX FAST FULL SCAN | IND_FUHE | 25251 | 123K| 22 (0)| 00:00:01 |
---------------------------------------------------------------------------------

ORACLE關於NULL的總結

相關文章