索引与Null值对于Hints及执行计划的影响

2019-10-16 23:29:31 来源: 随州信息港

由于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: 1601196873

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

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: 2928007915

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

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: 1601196873

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

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: 3593393735

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

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

查看本文来源

贵阳治疗妇科医院
郑州好的白癜风医院
廊坊治疗白带异常医院
南京牛皮癣医院哪家
浙江男科医院哪家好
本文标签: