索引与值对及执行计划的物业

由于B*Tree索引不存储Null值,所以在索引字段允许为空的情况下,某些Oracle查询不会使用索引.

很多时候,我们看似可以使用全索引扫描(Full Index Scan)的情况,可能Oracle就会因为Null值的存在而放弃索引.

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

我们看以下测试.

在username字段为Not Null时,Index Hints可以生效.

SQL create table t as select 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 select * from t where username=\'EYGLE\';

Execution Plan

----------------------------------------------------------

Plan hash value:

--------------------------------------------------------------------------

厂家是爱理不理的 Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time

--------------------------------------------------------------------------

0 | SELECT 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 select /*+ index(t,i_t) */ * from t where username=\'EYGLE\';

Execution Plan

----------------------------------------------------------

Plan hash value:

------------------------------------------------------------------------------------

Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time

------------------------------------------------------------------------------------

0 | SELECT 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

当索引字段允许为Null时,Oracle放弃此索引:

SQL alter table t modify (username null);

Table altered.

SQL select /*+ index(t,i_t) */ * from t;

Execution Plan

----------------------------------------------------------

Plan hash value:

--------------------------------------------------------------------------

Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time

--------------------------------------------------------------------------

0 | SELECT 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 select /*+ index(t,i_t) */ * from t;

Execution Plan

----------------------------------------------------------

Plan hash value:

------------------------------------------------------------------------------------

Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time

------------------------------------------------------------------------------------

0 | SELECT 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值对于索引及查询的影响.

查看本文来源