基于函数的索引浅析

--基于函数的索引浅析

EODA@PROD1> set echo on
EODA@PROD1> 
EODA@PROD1> create table emp
  2  as
  3  select *
  4  from scott.emp
  5  where 1=0;

Table created.

EODA@PROD1> 
EODA@PROD1> insert into emp
  2  (empno,ename,job,mgr,hiredate,sal,comm,deptno)
  3  select rownum empno,
  4  initcap(substr(object_name,1,10)) ename,
  5  substr(object_type,1,9) JOB,
  6  rownum MGR,
  7  created hiredate,
  8  rownum SAL,
  9  rownum COMM,
 10  (mod(rownum,4)+1)*10 DEPTNO
 11  from all_objects
 12  where rownum < 10000;

9999 rows created.

EODA@PROD1> set autotrace traceonly explain
EODA@PROD1> select * from emp where upper(ename) = 'KING';  --不建立索引的情况下查看执行计划

Execution Plan
----------------------------------------------------------
Plan hash value: 3956160932

--------------------------------------------------------------------------
| Id  | Operation	  | Name | Rows  | Bytes | Cost (%CPU)| Time	 |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |	 |     1 |    87 |    22   (0)| 00:00:01 |
|*  1 |  TABLE ACCESS FULL| EMP  |     1 |    87 |    22   (0)| 00:00:01 |
--------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - filter(UPPER("ENAME")='KING')

Note
-----
   - dynamic sampling used for this statement (level=2)

EODA@PROD1> create index emp_upper_idx on emp(upper(ename));  --建立一个基于函数的索引

Index created.

EODA@PROD1> exec dbms_stats.gather_table_stats(user,'EMP',cascade=>true);

PL/SQL procedure successfully completed.

EODA@PROD1> select * from emp where upper(ename) = 'KING';   --建立索引的情况下查看执行计划

Execution Plan
----------------------------------------------------------
Plan hash value: 1576737566

---------------------------------------------------------------------------------------------
| Id  | Operation		    | Name	    | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT	    |		    |	  2 |	110 |	  2   (0)| 00:00:01 |
|   1 |  TABLE ACCESS BY INDEX ROWID| EMP	    |	  2 |	110 |	  2   (0)| 00:00:01 |
|*  2 |   INDEX RANGE SCAN	    | EMP_UPPER_IDX |	  2 |	    |	  1   (0)| 00:00:01 |
---------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - access(UPPER("ENAME")='KING')

EODA@PROD1> drop index emp_upper_idx;

Index dropped.

EODA@PROD1> create index emp_idx on emp(ename);  --建立普通索引

Index created.

EODA@PROD1> exec dbms_stats.gather_table_stats(user,'EMP',cascade=>true);

PL/SQL procedure successfully completed.

EODA@PROD1> set autotrace traceonly explain
EODA@PROD1> select * from emp where upper(ename) = 'KING';  --查看执行计划仍然使用全表扫描

Execution Plan
----------------------------------------------------------
Plan hash value: 3956160932

--------------------------------------------------------------------------
| Id  | Operation	  | Name | Rows  | Bytes | Cost (%CPU)| Time	 |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |	 |   100 |  4400 |    22   (0)| 00:00:01 |
|*  1 |  TABLE ACCESS FULL| EMP  |   100 |  4400 |    22   (0)| 00:00:01 |
--------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - filter(UPPER("ENAME")='KING')


/* 使用基于函数的索引与之两者相比较，性能上的提升非常明显。 */

--参考来源《Oracle编程艺术深入理解 数据库 体系结构（第三版）》