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

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

索引字段允许为null时,oracle放弃此索引:

sql> alter table t modify (username null). table altered. sql> 0select /* index(t,i_t) */ * from t. execution plan ---------------------------------------------------------- plan hash value: 1601196873 -------------------------------------------------------------------------- | id | operation | name | rows | bytes | cost (%cpu)| time | -------------------------------------------------------------------------- | 0 | 0select statement | | 27 | 918 | 2 (0)| 00:00:01 | | 1 | table access full| t | 27 | 918 | 2 (0)| 00:00:01 | -------------------------------------------------------------------------- note ----- - dynamic sampling used for this statement

当该字段为not null时,索引可以被强制使用:

sql> alter table t modify (username not null). table altered. sql> 0select /* index(t,i_t) */ * from t. execution plan ---------------------------------------------------------- plan hash value: 3593393735 ------------------------------------------------------------------------------------ | id | operation | name | rows | bytes | cost (%cpu)| time | ------------------------------------------------------------------------------------ | 0 | 0select statement | | 27 | 918 | 2 (0)| 00:00:01 | | 1 | table access by index rowid| t | 27 | 918 | 2 (0)| 00:00:01 | | 2 | index full scan | i_t | 27 | | 1 (0)| 00:00:01 | ------------------------------------------------------------------------------------ note ----- - dynamic sampling used for this statement

这就是null值对于索引及查询的影响.