为了证明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@]