我有个表,我在dept_num上建立索引,并写了个dept_num为空的行进去,想测试,当索引列有空值时候索引无效,但我发现索引并没有无效啊?这是什么原因?
SQL> select dept_num,dept_name from test3 ;
DEP DEPT_NAME
--- ----------
100 Mis
200 Check
test------------这行的dept_num这个字段为空
Elapsed: 00:00:00.12
Execution Plan
----------------------------------------------------------
0SELECT STATEMENT Optimizer=CHOOSE
10 TABLE ACCESS (FULL) OF 'TEST3'
SQL> create index index_test3 on test3(dept_num) ;
Index created.
Elapsed: 00:00:00.03
SQL> analyze table test3 compute statistics ;
Table analyzed.
Elapsed: 00:00:00.01
SQL> analyze index index_test3 compute statistics ;
Index analyzed.
Elapsed: 00:00:00.04
SQL> select /*+index(test3 index_test3)*/ dept_num from test3 ;
DEP
---
100
200
Elapsed: 00:00:00.13
Execution Plan
----------------------------------------------------------
0SELECT STATEMENT Optimizer=CHOOSE (Cost=2 Card=3 Bytes=9)
10 TABLE ACCESS (FULL) OF 'TEST3' (Cost=2 Card=3 Bytes=9)
SQL>
SQL> select dept_num,dept_name from test3 where dept_num='100';
DEP DEPT_NAME
--- ----------
100 Mis
Elapsed: 00:00:00.12
Execution Plan
----------------------------------------------------------
0SELECT STATEMENT Optimizer=CHOOSE (Cost=2 Card=1 Bytes=7)
10 TABLE ACCESS (BY INDEX ROWID) OF 'TEST3' (Cost=2 Card=1 By
tes=7)
21 INDEX (RANGE SCAN) OF 'INDEX_TEST3' (NON-UNIQUE) (Cost=1
Card=1)