函数索引实验

T002表结构如下：
SQL> desc t002;
 名称            是否为空? 类型
 --------------- -------- ----------------
 ID1             NOT NULL NUMBER(10)
 ID2                      NUMBER(10)
 ID5                      DATE

插入100w行数据，其中id5字段中的日期为过去700天的分布。
declare
begin_rdm number(5);
v_sql varchar2(2000);
begin
for i in 1..1000000 loop
select trunc(dbms_random.value(0,700)) into begin_rdm from dual;
v_sql:='insert into t002 values ('||i||','||begin_rdm||',sysdate-'||begin_rdm||')';
execute immediate v_sql;
if mod(i,1000)=0 then
commit;
end if;
end loop;
end;
/

在id5字段上建立b*tree索引
create index ind_t002_id5 on t002(id5);

SQL> select * from t002 where to_char(id5,'yyyy-mm-dd')='2010-10-31';

已选择1251行。

已用时间:  00: 00: 00.96

执行计划
----------------------------------------------------------
Plan hash value: 1657573511

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

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

   1 - filter(TO_CHAR(INTERNAL_FUNCTION("ID5"),'yyyy-mm-dd')='2010-10-31
              ')

统计信息
----------------------------------------------------------
          0  recursive calls
          0  db block gets
       2916  consistent gets
       2673  physical reads
          0  redo size
      31238  bytes sent via SQL*Net to client
       1298  bytes received via SQL*Net from client
         85  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
       1251  rows processed

 

在id5字段上建立函数索引
create index ind_t002_id5_fun on t002(to_char(id5,'yyyy-mm-dd'));

SQL> select * from t002 where to_char(id5,'yyyy-mm-dd')='2010-10-31';

已选择1251行。

已用时间:  00: 00: 00.09

执行计划
----------------------------------------------------------
Plan hash value: 1028416900

------------------------------------------------------------------------------------------------
| Id  | Operation                   | Name             | Rows  | Bytes | Cost (%CPU)| Time     |
------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |                  |     1 |     6 |     3  (34)| 00:00:01 |
|   1 |  TABLE ACCESS BY INDEX ROWID| T002             |     1 |     6 |     3  (34)| 00:00:01 |
|*  2 |   INDEX RANGE SCAN          | IND_T002_ID5_FUN |  3712 |       |     6   (0)| 00:00:01 |
------------------------------------------------------------------------------------------------

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

   2 - access(TO_CHAR(INTERNAL_FUNCTION("ID5"),'yyyy-mm-dd')='2010-10-31')

统计信息
----------------------------------------------------------
          0  recursive calls
          0  db block gets
       1113  consistent gets
         90  physical reads
          0  redo size
      31238  bytes sent via SQL*Net to client
       1298  bytes received via SQL*Net from client
         85  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
       1251  rows processed

建立普通索引，对于SQL语句的条件不会走索引，采用全表扫描的方式。
建立函数索引，对于SQL语句的相应的条件，走了索引，逻辑读、物理读的开销都少了许多。