Oracle之函数索引之各种列的函数转换

--测函数索引前准备
drop table t purge;
create table t as select * from dba_objects;
create index idx_object_id on t(object_id);
create index idx_object_name on t(object_name);
create index idx_created on t(created);

--对列做UPPER操作，无法用到索引
set autotrace traceonly
set linesize 1000
---以下语句由于列运算，所以走的是全表扫描
select * from t  where upper(object_name)='T' ;
执行计划
--------------------------------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      |    12 |  2484 |   293   (1)| 00:00:04 |
|*  1 |  TABLE ACCESS FULL| T    |    12 |  2484 |   293   (1)| 00:00:04 |
--------------------------------------------------------------------------
统计信息
----------------------------------------------------------
          0  recursive calls
          0  db block gets
       1049  consistent gets
          0  physical reads
          0  redo size
       1500  bytes sent via SQL*Net to client
        415  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          2  rows processed
          
--去掉列的UPPER操作后立即用索引
select * from t  where  object_name='T' ;
执行计划
----------------------------------------------------------
Plan hash value: 1138138579


-----------------------------------------------------------------------------------------------
| Id  | Operation                   | Name            | Rows  | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |                 |     2 |   414 |     4   (0)| 00:00:01 |
|   1 |  TABLE ACCESS BY INDEX ROWID| T               |     2 |   414 |     4   (0)| 00:00:01 |
|*  2 |   INDEX RANGE SCAN          | IDX_OBJECT_NAME |     2 |       |     3   (0)| 00:00:01 |
-----------------------------------------------------------------------------------------------
统计信息
----------------------------------------------------------
          0  recursive calls
          0  db block gets
          6  consistent gets
          0  physical reads
          0  redo size
       1506  bytes sent via SQL*Net to client
        415  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          2  rows processed      
          
              
--如果必须用upper的条件，那你想用到索引，就得去建函数索引
create index idx_func_ojbnam on t(upper(object_name));
--继续执行，终于走索引了。


select * from t  where upper(object_name)='T' ;
执行计划
-----------------------------------------------------------------------------------------------
| Id  | Operation                   | Name            | Rows  | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |                 |   775 |   206K|   152   (0)| 00:00:02 |
|   1 |  TABLE ACCESS BY INDEX ROWID| T               |   775 |   206K|   152   (0)| 00:00:02 |
|*  2 |   INDEX RANGE SCAN          | IDX_FUNC_OJBNAM |   310 |       |     3   (0)| 00:00:01 |
-----------------------------------------------------------------------------------------------
统计信息
----------------------------------------------------------
          0  recursive calls
          0  db block gets
          6  consistent gets
          0  physical reads
          0  redo size
       1500  bytes sent via SQL*Net to client
        415  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          2  rows processed