文档介绍：该【2025年索引与Null值对于Hints及执行计划的影响（共2篇） 】是由【wawa】上传分享，文档一共【12】页，该文档可以免费在线阅读，需要了解更多关于【2025年索引与Null值对于Hints及执行计划的影响（共2篇） 】的内容，可以使用淘豆网的站内搜索功能，选择自己适合的文档，以下文字是截取该文章内的部分文字，如需要获得完整电子版，请下载此文档到您的设备，方便您编辑和打印。2025年索引与Null值对于Hints及执行计划的影响（共2篇）
篇1：索引与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
--------------------------------------------------------------------------
| 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值对于索引及查询的影响.
篇2：索引与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 >desc t
Name                                     Null?   Type
----------------------------------------- -------- ----------------------------
USERNAME                                 NOT NULL VARCHAR2(30)
PASSWORD                                          VARCHAR2(30)SQL>create index i_t on t(username);Index >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 statementSQL>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 >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 |