当前位置：首页 > 范文大全 > 工作计划>索引与Null值对于Hints及执行计划的影响数据库

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

2023-07-17 07:58:54 收藏本文下载本文

“vl”通过精心收集，向本站投稿了2篇索引与Null值对于Hints及执行计划的影响数据库，下面是小编收集整理后的索引与Null值对于Hints及执行计划的影响数据库，供大家参考借鉴，希望可以帮助到有需要的朋友。

篇1：索引与Null值对于Hints及执行计划的影响数据库

由于B*Tree索引不存储Null值,所以在索引字段允许为空的情况下,某些 Oracle 查询不会使用索引. 很多时候,我们看似可以使用全索引扫描(Full Index Scan)的情况,可能Oracle就会因为Null值的存在而放弃索引. 在此情况下即使使用Hints,Oracle也不会使用索引,其根

由于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 - aclearcase/“ target=”_blank“ >ccess(”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值对于索引及查询的影响.

原文地址：www.eygle.com/archives//02/index_null_hints_explain.html

原文转自：www.ltesting.net

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

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

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

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

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

| 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

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

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

| 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

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

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

| 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

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

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

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

【索引与Null值对于Hints及执行计划的影响数据库】相关文章：

1.disablelogging对于性能的影响数据库

《索引与Null值对于Hints及执行计划的影响数据库.doc》

将本文的Word文档下载到电脑，方便收藏和打印

推荐度：

点击下载文档

文档为doc格式

工作计划图文推荐

索引与Null值对于Hints及执行计划的影响数据库相关文章