索引与Null值对Hints及执行计划的影响(1)

文章作者 100test 发表时间 2007:03:14 13:51:29
来源 100Test.Com百考试题网

　b*tree索引不存储null值,所以在索引字段允许为空的情况下,某些oracle查询不会使用索引.

很多时候,我们看似可以使用全索引扫描(full index scan)的情况,可能oracle就会因为null值的存在而放弃索引

在此情况下即使使用hints,oracle也不会使用索引,其根本原因就是因为null值的存在.

我们看以下测试.

在username字段为not null时,index hints可以生效.

sql> create table t as 0select username,password from dba_users. table created. sql> desc t name null? type ----------------------------------------- -------- ---------------------------- username not null varchar2(30) password varchar2(30) sql> create index i_t on t(username). index created. sql> set autotrace trace explain sql> 0select * from t where username=eygle. execution plan ---------------------------------------------------------- plan hash value: 1601196873 -------------------------------------------------------------------------- | id | operation | name | rows | bytes | cost (%cpu)| time | -------------------------------------------------------------------------- | 0 | 0select statement | | 1 | 34 | 2 (0)| 00:00:01 | |* 1 | table access full| t | 1 | 34 | 2 (0)| 00:00:01 | -------------------------------------------------------------------------- predicate information (identified by operation id): --------------------------------------------------- 1 - filter("username"=eygle) note ----- - dynamic sampling used for this statement sql> set linesize 120 sql> 0select /* index(t,i_t) */ * from t where username=eygle. execution plan ---------------------------------------------------------- plan hash value: 2928007915 ------------------------------------------------------------------------------------ | id | operation | name | rows | bytes | cost (%cpu)| time | ------------------------------------------------------------------------------------ | 0 | 0select statement | | 1 | 34 | 2 (0)| 00:00:01 | | 1 | table access by index rowid| t | 1 | 34 | 2 (0)| 00:00:01 | |* 2 | index range scan | i_t | 1 | | 1 (0)| 00:00:01 | ------------------------------------------------------------------------------------ predicate information (identified by operation id): --------------------------------------------------- 2 - access("username"=eygle) note ----- - dynamic sampling used for this statement