index 包含null值得簡單測試

為了證明index對null不會紀錄，特別做了以下驗證:
目的透過察看全為null的列，和非null的列上的索引的葉子塊和總的block進行比較，得到索引是否會紀錄null值，或者說index是紀錄 null 相關的 rowid呢？還是任何與null相關的列都不紀錄。 另外第二個實驗用來說明直方圖對存在null值的索引列的作用。

先把結論說一下:
1. 索引列不紀錄任何null值相關的內容，如果是null,不會產生任何索引條目;
2. 索引列出現在謂詞中，如果查詢條件為is null, 走全表；如果條件為 is not null, <> 某個具體的值，走index full scan; 如果條件為等值查詢，走索引範圍掃描；這從另一個側面說明索引列中包含空值時，如果要查詢和列中空值相關的行，oracle不能透過index得到 null的資訊，只有走全表，從而證明index內並沒有維護空值；

1. index have all null value;

kl@k02> drop table t1;

Table dropped.

kl@k02> create table t1 (a varchar2(20), b varchar2(20));

Table created.

kl@k02> begin
2 for i in 1..10000 loop
3 insert into t1 values ('','bbbbbbbbbbbbbb');
4 end loop;
5 commit;
6 end;
7 /

PL/SQL procedure successfully completed.

kl@k02> create index t1_idnull on t1(a);

Index created.

kl@k02> create index t1_idva on t1(b);

Index created.

kl@k02>
kl@k02> exec dbms_stats.gather_table_stats(user,'t1',cascade=>true);

PL/SQL procedure successfully completed.

kl@k02>
kl@k02> col index_name for a20
kl@k02> col segment_name for a20
kl@k02>
kl@k02> select index_name,NUM_ROWS,BLEVEL,LEAF_BLOCKS from dba_indexes
2 where table_name='T1' and table_owner='KL';

INDEX_NAME NUM_ROWS BLEVEL LEAF_BLOCKS
-------------------- ---------- ---------- -----------
T1_IDNULL 0 0 0
T1_IDVA 10000 1 37

kl@k02>
kl@k02> select SEGMENT_NAME,extents,BLOCKS
2 from dba_segments where SEGMENT_NAME in ('T1_IDNULL','T1_IDVA');

SEGMENT_NAME EXTENTS BLOCKS
-------------------- ---------- ----------
T1_IDNULL 1 8
T1_IDVA 6 48


2. index have 1/10 not null;

kl@k02> create table t1 (a number, b varchar2(20));

Table created.

kl@k02> begin
2 for i in 1..10000 loop
3 if mod(i,1000)=0 then
4 insert into t1 values (i,'bbbbbbbbbbbbbb');
5 else
6 insert into t1 values (null,'bbbbbbbbbbbbbb');
7 end if;
8 end loop;
9 commit;
10 end;
11 /

PL/SQL procedure successfully completed.

kl@k02>
kl@k02> create index t1_idnull on t1(a);

Index created.

kl@k02> set autotrace traceonly exp
kl@k02>
kl@k02> exec dbms_stats.gather_table_stats(user,'t1',cascade=>true,method_opt=>'for all columns

PL/SQL procedure successfully completed.

kl@k02> select a from t1 where a is null;

Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=5 Card=9990 Bytes=19980)
1 0 TABLE ACCESS (FULL) OF 'T1' (Cost=5 Card=9990 Bytes=19980)



kl@k02> select a from t1 where a is not null;

Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=1 Card=10 Bytes=20)
1 0 INDEX (FULL SCAN) OF 'T1_IDNULL' (NON-UNIQUE) (Cost=1 Card=10 Bytes=20)



kl@k02> select a from t1 where a=10098;

Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=1 Card=1 Bytes=2)
1 0 INDEX (RANGE SCAN) OF 'T1_IDNULL' (NON-UNIQUE) (Cost=1 Card=1 Bytes=2)



kl@k02> select a from t1 where a<>10098;

Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=1 Card=9000 Bytes=18000)
1 0 INDEX (FULL SCAN) OF 'T1_IDNULL' (NON-UNIQUE) (Cost=1 Card=9000 Bytes=18000)

[@more@]