再說索引與Null值對於Hints及執行計劃的影響

今天看了大師eygle的《索引與Null值對於Hints及執行計劃的影響》一文，受益頗多，連結如下
http://www.eygle.com/archives/2006/02/index_null_hints_explain.html

只是有些地方感覺說的不是太清楚，我認為有必要總結出來。以下是用到的表。

SQL> create table scott.t as select username,password from dba_users;

Table created.

SQL> conn scott/tiger;

SQL> create index idx_t on t(username);

Index created.

        由於B*Tree索引不儲存Null值,所以在索引欄位允許為空的情況下,某些Oracle查詢不會使用索引.這裡說的某些Oracle查詢就是不帶where條件，或者帶where條件，但是where條件不包含索引欄位的。很多時候,我們看似可以使用全索引掃描(Full Index Scan)的情況,可能Oracle就會因為Null值的存在而放棄索引.

比如對於sql語句：select /*+ index(t,idx_t) */ * from t;
如果table t 的username欄位有not null約束，加Hints就會用到索引，如果username欄位沒有not null約束（允許為空），加了Hints也用不到索引：

SQL> desc t;
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 USERNAME                                  NOT NULL VARCHAR2(30)
 PASSWORD                                           VARCHAR2(30)

SQL> set autotrace trace explain

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

Execution Plan
----------------------------------------------------------
Plan hash value: 6977672

--------------------------------------------------------------------------------------
| Id  | Operation                   | Name   | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |        |     1 |    15 |     2   (0)| 00:00:01 |
|*  1 |  TABLE ACCESS BY INDEX ROWID| TT     |     1 |    15 |     2   (0)| 00:00:01 |
|*  2 |   INDEX RANGE SCAN          | IDX_TT |     3 |       |     1   (0)| 00:00:01 |
--------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

SQL> alter table t modify (username null);Table altered.

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

Execution Plan
----------------------------------------------------------
Plan hash value: 264906180

--------------------------------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      |     6 |    90 |     3   (0)| 00:00:01 |
|   1 |  TABLE ACCESS FULL| T   |     6 |    90 |     3   (0)| 00:00:01 |
--------------------------------------------------------------------------

is null條件當然是不查B*Tree索引的，Bitmap索引則可以，where字句中沒有索引欄位的也不會用到索引。其他情況是可以用到索引的，不管索引列是否是not null的，也不管你是否指定了Hints。

比如以下這兩條語句都會查B*Tree索引的：

SQL> select /*+ index(t,idx_t) */ * from t where name='beijing';

SQL> select * from t where name='beijing';

 

來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/8554499/viewspace-578198/，如需轉載，請註明出處，否則將追究法律責任。