当然是可以的了,动手实践下不就知道了:
sys@orcl>create table person (id number,name varchar2(20),age number);
表已创建。
sys@orcl>create or replace function LessThan(age number,num number)
2 return varchar2 deterministic
3 as
4 begin
5 if age < num then
6 return 'TRUE';
7 else
8 return 'FALSE';
9 end if;
10 end;
11 /
函数已创建。
sys@orcl>create index person_ind1 on person(lessthan(age,20));
索引已创建。
sys@orcl>insert into person values(1,'zhy',20);
已创建 1 行。
sys@orcl>commit;
sys@orcl>set autotrace traceonly explain;
sys@orcl>select * from person where lessthan(age,20) = 'TRUE';
执行计划
----------------------------------------------------------
Plan hash value: 1398264976
-------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 38 | 1 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID| PERSON | 1 | 38 | 1 (0)| 00:00:01 |
|* 2 | INDEX RANGE SCAN | PERSON_IND1 | 1 | | 1 (0)| 00:00:01 |
-------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("SYS"."LESSTHAN"("AGE",20)='TRUE')
Note
-----
- dynamic sampling used for this statement
索引被用了,不过下面情况当然就不行了:
sys@orcl>select * from person where lessthan(age,21) = 'TRUE';
执行计划
----------------------------------------------------------
Plan hash value: 1493655343
----------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 38 | 2 (0)| 00:00:01 |
|* 1 | TABLE ACCESS FULL| PERSON | 1 | 38 | 2 (0)| 00:00:01 |
----------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("LESSTHAN"("AGE",21)='TRUE')
Note
-----
- dynamic sampling used for this statement