Oracle索引能否儲存NULL值？詢問周圍的朋友，基本都認為不能儲存NULL值，但也有一些持反對意見的人，認為其他資料庫索引能儲存NULL值，為什麼ORACLE不可以？那到底實際情況什麼樣子？無論哪種觀點，總應該找權威的觀點來做理論支援，不能人云亦云(做技術的人就是這麼較勁，來不得半點馬虎)。在翻閱了大量資料後，蒐集到相關內容如下：

1.B*Tree indexes, except in the special case of cluster B*Tree indexes, do not store completely Null entries, but bitmap and cluster indexes do.--《Expert one on Oracle》

2. Columns with many duplicate values or many rows with NULL values should not be included or should be the last-named columns in the index definition. --Oracle Online Documention.

對上面兩條內容合併一下可以得出結論：B樹索引可以儲存NULL值，但不允許索引欄位全部為NULL，若索引欄位全部為NULL，索引不會儲存。

下面我們透過實驗進行說明。

Oracle版本資訊：

SELECT * FROM v$version WHERE rownum=1;

BANNER

-------------------------------------------------------------------------------

Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production

1.create測試表，並插入相關資料

drop table t purge;

create table t (id number,data varchar2(100));

insert into t values(1,'a');

insert into t values(2,'b');

insert into t values(3,'c');

insert into t values(100,'aaa');

insert into t values(103,'ccc');

commit;

2.建立索引

create index t_ind_id on t(id);

3.分析索引

透過analyze index分析索引

analyze index t_ind_id validate structure;

select name, lf_rows from index_stats;

NAME LF_ROWS

------------------------------ ----------

T_IND_ID 5

進一步treedump索引層次結構

select OBJECT_ID,DATA_OBJECT_ID from dba_objects where owner='SCOTT' and OBJECT_NAME='T_IND_ID';

OBJECT_ID DATA_OBJECT_ID

---------- --------------

17477 17477

alter session set events 'immediate trace name treedump level 17477';

select tracefile from v$process where addr=(select paddr from v$session where sid=(select distinct sid from v$mystat));

輸出結果如下：

*** 2017-02-22 03:42:20.194

----- begin tree dump

leaf: 0x1002823 16787491 (0: nrow: 5 rrow: 5)

----- end tree dump

透過trace檔案可以看到索引共記錄了5個條目，其中0x1002823為索引的16進位制地址,16787491對應索引的10進位制地址。

將地址轉化為具體的資料檔案和塊

SELECT dbms_utility.data_block_address_file(16787491),dbms_utility.data_block_address_block(16787491) FROM dual;

DBMS_UTILITY.DATA_BLOCK_ADDRESS_FILE(16787491) DBMS_UTILITY.DATA_BLOCK_ADDRESS_BLOCK(16787491)

---------------------------------------------- -----------------------------------------------

4 10275

透過命令將該塊的內容轉儲出來

alter system dump datafile 4 block 10275;

Leaf block dump

===============

header address 139905684912740=0x7f3e54a6ba64

kdxcolev 0

KDXCOLEV Flags = - - -

kdxcolok 0

kdxcoopc 0x80: opcode=0: iot flags=--- is converted=Y

kdxconco 2

kdxcosdc 0

kdxconro 5

kdxcofbo 46=0x2e

kdxcofeo 7971=0x1f23

kdxcoavs 7925

kdxlespl 0

kdxlende 0

kdxlenxt 0=0x0

kdxleprv 0=0x0

kdxledsz 0

kdxlebksz 8032

row#0[8020] flag: ------, lock: 2, len=12

col 0; len 2; (2): c1 02

col 1; len 6; (6): 01 00 24 1c 00 00

row#1[7971] flag: ------, lock: 2, len=12

col 0; len 2; (2): c1 03

col 1; len 6; (6): 01 00 24 1c 00 04

row#2[8008] flag: ------, lock: 2, len=12

col 0; len 2; (2): c1 04

col 1; len 6; (6): 01 00 24 1c 00 01

row#3[7996] flag: ------, lock: 2, len=12

col 0; len 2; (2): c2 02

col 1; len 6; (6): 01 00 24 1c 00 02

row#4[7983] flag: ------, lock: 2, len=13

col 0; len 3; (3): c2 02 04

col 1; len 6; (6): 01 00 24 1c 00 03

----- end of leaf block dump -----

End dump data blocks tsn: 4 file#: 4 minblk 10275 maxblk 10275

其中col 0--索引的鍵值，col1--rowid(對於葉子節點來說)，透過dbms_stats.convert_raw_value轉換可以確認和實際情況一樣

declare n number;

begin

dbms_stats.convert_raw_value('c102',n);

dbms_output.put_line('c102--->' || n);

dbms_stats.convert_raw_value('c103',n);

dbms_output.put_line('c103--->' || n);

dbms_stats.convert_raw_value('c104',n);

dbms_output.put_line('c104--->' || n);

dbms_stats.convert_raw_value('c202',n);

dbms_output.put_line('c202--->' || n);

dbms_stats.convert_raw_value('c20204',n);

dbms_output.put_line('c20204--->' || n);

end;

/

c102--->1

c103--->2

c104--->3

c202--->100

c20204--->103

4.上面囉囉嗦嗦寫了那麼多，只為證明一個問題：索引準確儲存了5個記錄的相關索引鍵值。下面進入正題

插入幾條id為空的記錄，再看看索引的情況

insert into t(data) values('d');

insert into t(data) values('eeeee');

insert into t(data) values('ffffff');

commit;

透過analyze index分析索引

analyze index t_ind_id validate structure;

select name, lf_rows from index_stats;

NAME LF_ROWS

------------------------------ ----------

T_IND_ID 5

再次treedump索引層次結構

alter session set events 'immediate trace name treedump level 17477';

select tracefile from v$process where addr=(select paddr from v$session where sid=(select distinct sid from v$mystat));

*** 2017-02-22 03:59:08.032

leaf: 0x1002823 16787491 (0: nrow: 5 rrow: 5)

----- end tree dump

細心的你發現什麼了嗎？咦，為什麼我剛剛插入了3條記錄，索引的條目並沒有增長呢？B樹索引可以儲存NULL值，但不允許索引欄位全部為NULL，若索引欄位全部為NULL，索引不會儲存。 因為該索引為單列索引，且插入的資料索引欄位為NULL，等於整個索引鍵值為NULL，不會儲存，因此索引條目沒有增加。

趁熱打鐵，我們再建立一個複合索引的例子來印證剛才的理論。

1.create測試表，並插入相關資料

drop table t1 purge;

create table t1 (id number,data varchar2(100));

insert into t1 values(1,'a');

commit;

2.建立索引

create index t1_ind_id on t1(id,data);

3.分析索引

透過analyze index分析索引

analyze index t1_ind_id validate structure;

select name, lf_rows from index_stats;

NAME LF_ROWS

------------------------------ ----------

T_IND_ID 1

insert into t1(data) values('b');

commit;

analyze index t1_ind_id validate structure;

select name, lf_rows from index_stats;

NAME LF_ROWS

------------------------------ ----------

T1_IND_ID 2

insert into t1(id) values(3);

commit;

analyze index t1_ind_id validate structure;

select name, lf_rows from index_stats;

NAME LF_ROWS

------------------------------ ----------

T1_IND_ID 3

insert into t1 values(NULL,NULL);

commit;

analyze index t1_ind_id validate structure;

select name, lf_rows from index_stats;

NAME LF_ROWS

------------------------------ ----------

T1_IND_ID 3

發現了什麼？當id為NULL，data不是NULL或者id不是NULL，data為NULL的時候，索引條目都會增加，但當id，data都為NULL的時候，索引條目並沒有增加。

treedump索引層次結構，可以清楚的發現複合索引的鍵值可以部分為NULL。

row#0[8018] flag: ------, lock: 0, len=14

col 0; len 2; (2): c1 02

col 1; len 1; (1): 61

col 2; len 6; (6): 01 00 28 2c 00 00

row#1[7993] flag: ------, lock: 2, len=13

col 0; len 2; (2): c1 04

col 1; NULL

col 2; len 6; (6): 01 00 28 2c 00 02

row#2[8006] flag: ------, lock: 0, len=12

col 0; NULL

col 1; len 1; (1): 62

col 2; len 6; (6): 01 00 28 2c 00 01

----- end of leaf block dump -----

透過複合索引的實驗，更加清楚的說明了B樹索引可以儲存NULL值，但不允許索引欄位全部為NULL，若索引欄位全部為NULL，索引不會儲存。
瞭解了上述內容後，對我們實際工作有何意義？建立索引時應該考慮什麼？

1.若列對應的業務資料確實不存在NULL值的情況，建議建立表語句新增NOT NULL的約束

2.列對應的業務資料可能存在NULL值的情況，可以透過如下方法建立索引:

a)函式索引

create index t_ind_id on t(nvl(id,0));

b)偽列

create index t_ind_id on t(id,0);

c)複合索引

create index t_ind_id on t(id,data);

延伸：

明白了索引不能儲存全部NULL值的鍵值，就解釋了某些執行計劃走全表掃描的原因了。

drop table t purge;

create table t (id number,data varchar2(100));

begin

for i in 1..10000 loop

insert into t values(trunc(dbms_random.value(1,1000)),dbms_random.string('a',5));

end loop;

commit;

end loop;

create index t_id on t(id);

exec dbms_stats.gather_table_stats(USER,'T',CASCADE=>TRUE);

SQL> SELECT count(*) FROM t;

COUNT(*)

----------

10000

Execution Plan

----------------------------------------------------------

Plan hash value: 2966233522

-------------------------------------------------------------------

-------------------------------------------------------------------

| 0 | SELECT STATEMENT | | 1 | 9 (0)| 00:00:01 |

| 1 | SORT AGGREGATE | | 1 | | |

| 2 | TABLE ACCESS FULL| T | 10000 | 9 (0)| 00:00:01 |

-------------------------------------------------------------------

為什麼執行計劃是TABLE ACCESS FULL，因為欄位id沒有NOT NULL限制，如果走索引可能會漏掉id=NULL值的記錄，從而統計結果有問題。

alter table t modify id not null;

SQL> SELECT count(*) FROM t;

COUNT(*)

----------

10000

Execution Plan

----------------------------------------------------------

Plan hash value: 3689807224

----------------------------------------------------------------------

----------------------------------------------------------------------

| 0 | SELECT STATEMENT | | 1 | 7 (0)| 00:00:01 |

| 1 | SORT AGGREGATE | | 1 | | |

| 2 | INDEX FAST FULL SCAN| T_ID | 10000 | 7 (0)| 00:00:01 |

----------------------------------------------------------------------

可以看到執行計劃從TABLE ACCESS FULL轉換成了INDEX FAST FULL SCAN,最佳化了SQL的執行效率。

淺談索引序列之是否可以儲存NULL值？

相關文章