测试表:
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 为空的行给漏了,但既然都为空,也就没东西展示了,按理也可以直接使用索引才对呢.