测试表:
create table products(prod_id number(10), prod_name varchar(20));
create index idx_products_1 on products(upper(prod_name));
SQL> select upper(prod_name) from products a ;
Execution Plan
----------------------------------------------------------
Plan hash
value: 1954719464
------------------------------------------------------------------------------
| Id | Operation |
Name | Rows | Bytes | Cost (%CPU)|
Time |
------------------------------------------------------------------------------
| 0 | SELECT STATEMENT
| | 101
| 1212 | 3 (0)| 00:00:01 |
| 1 | TABLE ACCESS FULL| PRODUCTS | 101
| 1212 | 3 (0)| 00:00:01 |
------------------------------------------------------------------------------
Note
-----
- dynamic sampling used for this
statement (level=2)
--使用hint 强制使用索引也同样无效
SQL> select /*+index(a IDX_PRODUCTS_1)*/ upper(prod_name) from products a;
Execution Plan
----------------------------------------------------------
Plan hash
value: 1954719464
------------------------------------------------------------------------------
| Id | Operation |
Name | Rows | Bytes | Cost (%CPU)|
Time |
------------------------------------------------------------------------------
| 0 | SELECT STATEMENT
| | 101
| 1212 | 3 (0)| 00:00:01 |
| 1 | TABLE ACCESS FULL| PRODUCTS | 101
| 1212 | 3 (0)| 00:00:01 |
------------------------------------------------------------------------------
Note
-----
- dynamic sampling used for this
statement (level=2)
--加入条件 not null 后正常使用索引扫描
SQL> select upper(prod_name) from products a where upper(prod_name) is not null;
Execution Plan
----------------------------------------------------------
Plan hash
value: 3690238772
-----------------------------------------------------------------------------------
| Id | Operation |
Name | Rows |
Bytes | Cost (%CPU)| Time |
-----------------------------------------------------------------------------------
| 0 | SELECT STATEMENT
|
| 5 | 60 |
1 (0)| 00:00:01 |
|* 1 | INDEX FULL SCAN |
IDX_PRODUCTS_1 | 5 | 60
| 1 (0)| 00:00:01 |
-----------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter(UPPER("PROD_NAME") IS NOT NULL)
Note
-----
- dynamic sampling used for this
statement (level=2)
--修改表prod_name 字段为非空后同样可使用索引
SQL> alter table products modify prod_name not null;
Table altered.
SQL> select upper(prod_name) from products a ;
Execution Plan
----------------------------------------------------------
Plan hash
value: 3690238772
-----------------------------------------------------------------------------------
| Id | Operation |
Name | Rows |
Bytes | Cost (%CPU)| Time |
-----------------------------------------------------------------------------------
| 0 | SELECT STATEMENT
|
| 101 | 1212 | 1 (0)|
00:00:01 |
| 1 | INDEX FULL SCAN | IDX_PRODUCTS_1
| 101 | 1212 | 1 (0)|
00:00:01 |
-----------------------------------------------------------------------------------
Note
-----
- dynamic sampling used for this
statement (level=2)
oracle这样做只是为了避免将prod_name 为空的行给漏了,但既然都为空,也就没东西展示了,按理也可以直接使用索引才对呢.
关于null影响索引使用的一例子,布布扣,bubuko.com
原文：http://www.cnblogs.com/bowshy/p/3648364.html