SQL> create table test(c1 int,c2 varchar2(10));

Table created.

SQL> create index test_idx_01 on test(to_date(c2,'YYMMDD'));
create index test_idx_01 on test(to_date(c2,'YYMMDD'))
                                         *
ERROR at line 1:
ORA-01743: only pure functions can be indexed

对于FBI,所使用的函数必须是DETERMINISTIC,就是说任何时候被调用其始终返回相同的结果,在上面因为年份只取了后两位,这是一个不确定的值,因而报错了:

SQL> create index test_idx_01 on test(to_date('20'||c2,'YYYYMMDD'));

Index created.

SQL>