但是可以骗oracle
SQL> create table tcase(d date,n number);
表已创建。
已用时间: 00: 00: 00.23
SQL> create function fcase return date is begin return sysdate; end;
2 /
函数已创建。
已用时间: 00: 00: 00.32
SQL> create index ti on tcase(case d when fcase then 1 else 2 end);
create index ti on tcase(case d when fcase then 1 else 2 end)
*
第 1 行出现错误:
ORA-30553: 函数不能确定
已用时间: 00: 00: 00.04
SQL> create function fcase2 return date DETERMINISTIC is begin return sysdate; end;
2 /
函数已创建。
已用时间: 00: 00: 00.01
SQL> create index ti on tcase(case d when fcase2 then 1 else 2 end);
索引已创建。
已用时间: 00: 00: 00.14
SQL> select * from tcase where (case d when fcase2 then 1 else 2 end)=1;
未选定行
已用时间: 00: 00: 00.03
SQL> set autot on
SQL> /
未选定行
已用时间: 00: 00: 00.00
执行计划
----------------------------------------------------------
Plan hash value: 3675649526
-------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 25 | 1 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID| TCASE | 1 | 25 | 1 (0)| 00:00:01 |
|* 2 | INDEX RANGE SCAN | TI | 1 | | 1 (0)| 00:00:01 |
-------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access(CASE "D" WHEN "HR"."FCASE2"() THEN 1 ELSE 2 END =1)
Note
-----
- dynamic sampling used for this statement (level=2)
统计信息
----------------------------------------------------------
0 recursive calls
0 db block gets
1 consistent gets
0 physical reads
0 redo size
344 bytes sent via SQL*Net to client
408 bytes received via SQL*Net from client
1 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
0 rows processed