最近在项目实施过程中碰到一个数据移植问题：新系统中某个表的表结构，只有“缴费开始年月”和“缴费月数”两个字段，没有“缴费终止年月”字段；当客户要求查询具体某个月份是否缴费时，由于没有“缴费终止年月”这个字段，数据库查询时会按全表扫描的方式遍历所有数据。

解决方案：
考虑到ORACLE数据库提供函数索引的功能，采用自定义函数伪造一个数据列——缴费终止年月，然后在再在“缴费开始年月”和伪列“缴费终止年月”建立一个索引。
当客户查询具体某个月份是否缴费时，SQL会按照此索引遍历数据。

实现步骤：

第一步，创建自定义函数
create or replace function zzny(prm_beginym varchar2,prm_ys number) return varchar2 deterministic is
  Result varchar2(6);
begin
 
  if lengthb(prm_beginym)<>6 then
     return '';
  end if;
 
  begin
       Result:=to_char(add_months(to_date(prm_beginym,'yyyymm'),prm_ys),'yyyymm');
  exception
           when others then
                return '';
  end;
 
  return(Result);
end zzny;

Oracle的性能参考手册上描述了函数的DETERMINISTIC特性，定义了这种类型的函数给定了确定的输入值，它的输出值也是确定了。

如果在函数索引或物化视图中使用自定义的函数，则这个函数必须声明为DETERMINISTIC。

不过，DETEMINISTIC特性必须由函数的定义者来进行保证，Oracle并不对这个特性进行检验，如果强制将非DETEMINISTIC函数定义为DETEMINISTIC类型，则查询可能会得到错误的结果。

第二步，进行测试
SQL> select * from v$version;

BANNER
----------------------------------------------------------------
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Prod
PL/SQL Release 10.2.0.1.0 - Production
CORE    10.2.0.1.0      Production
TNS for 32-bit Windows: Version 10.2.0.1.0 - Production
NLSRTL Version 10.2.0.1.0 - Production

创建表结构，其中字段YM为开始年月，JFYS为缴费月数。

SQL> create table FUNCINDEX
  2  (
  3    YM   VARCHAR2(6) not null,
  4    JFYS NUMBER default 1
  5  );

表已创建。

最重要的一步——创建函数索引
SQL> create index IND_FUNCINDEX_1 on FUNCINDEX (YM, ZZNY(YM,JFYS));

索引已创建。

收集统计信息

SQL> execute dbms_stats.gather_table_stats(ownname => user,tabname => 'FUNCINDEX
',cascade => TRUE);

PL/SQL 过程已成功完成。

SQL> insert into funcindex values('200901',3);

已创建 1 行。

SQL> commit;

提交完成。

SQL> set autotrace on
SQL> select * from funcindex where ym<='200903' and zzny(ym,jfys)>='200903';

YM           JFYS
------ ----------
200901          3


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

--------------------------------------------------------------------------------

---------------

| Id  | Operation                   | Name            | Rows  | Bytes | Cost (%C

PU)| Time     |

--------------------------------------------------------------------------------

---------------

|   0 | SELECT STATEMENT            |                 |     1 |    18 |     0
(0)| 00:00:01 |

|   1 |  TABLE ACCESS BY INDEX ROWID| FUNCINDEX       |     1 |    18 |     0
(0)| 00:00:01 |

|*  2 |   INDEX RANGE SCAN          | IND_FUNCINDEX_1 |     1 |       |     0
(0)| 00:00:01 |

--------------------------------------------------------------------------------

---------------


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

   2 - access("TEST"."ZZNY"("YM","JFYS")>='200903' AND "YM"<='200903')
       filter("TEST"."ZZNY"("YM","JFYS")>='200903')


统计信息
----------------------------------------------------------
         40  recursive calls
          0  db block gets
         14  consistent gets
          0  physical reads
          0  redo size
        463  bytes sent via SQL*Net to client
        385  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          1  rows processed

通过以上的执行计划可以看出，函数索引起到了一个数据伪列的作用。

SQL> execute dbms_stats.delete_table_stats(ownname => user,tabname => 'FUNCINDEX
');

PL/SQL 过程已成功完成。

SQL> select * from funcindex where ym<='200903' and zzny(ym,jfys)>='200903';

YM           JFYS
------ ----------
200901          3


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

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

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

   1 - filter("YM"<='200903' AND "ZZNY"("YM","JFYS")>='200903')

Note
-----
   - dynamic sampling used for this statement


统计信息
----------------------------------------------------------
          0  recursive calls
          0  db block gets
          2  consistent gets
          0  physical reads
          0  redo size
        463  bytes sent via SQL*Net to client
        385  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          1  rows processed

删除统计信息后，执行计划采用的是全表扫描的方式。

SQL> execute dbms_stats.gather_table_stats(ownname => user,tabname => 'FUNCINDEX
',cascade => TRUE);

PL/SQL 过程已成功完成。

SQL> select * from funcindex where ym<='200903' and zzny(ym,jfys)>='200903';

YM           JFYS
------ ----------
200901          3


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

--------------------------------------------------------------------------------

---------------

| Id  | Operation                   | Name            | Rows  | Bytes | Cost (%C

PU)| Time     |

--------------------------------------------------------------------------------

---------------

|   0 | SELECT STATEMENT            |                 |     1 |    10 |     2
(0)| 00:00:01 |

|   1 |  TABLE ACCESS BY INDEX ROWID| FUNCINDEX       |     1 |    10 |     2
(0)| 00:00:01 |

|*  2 |   INDEX RANGE SCAN          | IND_FUNCINDEX_1 |     1 |       |     1
(0)| 00:00:01 |

--------------------------------------------------------------------------------

---------------


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

   2 - access("TEST"."ZZNY"("YM","JFYS")>='200903' AND "YM"<='200903')
       filter("TEST"."ZZNY"("YM","JFYS")>='200903')


统计信息
----------------------------------------------------------
          0  recursive calls
          0  db block gets
          2  consistent gets
          0  physical reads
          0  redo size
        463  bytes sent via SQL*Net to client
        385  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          1  rows processed

SQL>

重新收集统计信息后，执行计划重新走函数索引。