ITPub博客

首页 > 应用开发 > IT综合 > index 包含null值得简单测试

index 包含null值得简单测试

原创 IT综合 作者:kl911 时间:2009-12-26 09:09:58 0 删除 编辑
为了证明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@]

来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/661919/viewspace-1029999/,如需转载,请注明出处,否则将追究法律责任。

下一篇: 关于USE_NL的使用
请登录后发表评论 登录
全部评论

注册时间:2008-01-03

  • 博文量
    20
  • 访问量
    318516