1.模拟环境
创建表插入数据库
[oracle@node1 ~]$ sqlplus chf/xifenfei
SQL*Plus: Release 11.2.0.3.0 Production on Mon Jan 9 16:27:19 2012
Copyright (c) 1982, 2011, Oracle. All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, Oracle Label Security, OLAP, Data Mining,
Oracle Database Vault and Real Application Testing options
SQL> create table t_xifenfei(id number,intime date);
Table created.
SQL> DECLARE
2 i NUMBER;
3 BEGIN
4 FOR i IN 1..1000 LOOP
5 INSERT INTO t_xifenfei VALUES(i,SYSDATE-i);
6 END LOOP;
7 COMMIT;
8 END;
9 /
PL/SQL procedure successfully completed.
SQL> select count(*) from t_xifenfei;
COUNT(*)
----------
1000
SQL> exec dbms_stats.gather_table_stats(USER,'T_XIFENFEI',cascade => TRUE);
PL/SQL procedure successfully completed.
2.无index查询
SQL> set autot trace exp stat
Execution Plan
----------------------------------------------------------
Plan hash value: 548923532
--------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 10 | 120 | 3 (0)| 00:00:01 |
|* 1 | TABLE ACCESS FULL| T_XIFENFEI | 10 | 120 | 3 (0)| 00:00:01 |
--------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter(NVL("INTIME",SYSDATE@!)>=TO_DATE(' 2011-12-31 00:00:00',
'syyyy-mm-dd hh24:mi:ss'))
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
7 consistent gets
0 physical reads
0 redo size
770 bytes sent via SQL*Net to client
519 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
9 rows processed
SQL> set autot off
这里只是做了一个简单的查询，因为这个nvl(intime,sysdate)的条件，无法使用正常的index，所以没有建立intime索引的测试。
3.尝试创建index
SQL> create index in_t_xifenfei on t_xifenfei (nvl(intime,sysdate)) online nologging;
create index in_t_xifenfei on t_xifenfei (nvl(intime,sysdate)) online nologging
*
ERROR at line 1:
ORA-01743: only pure functions can be indexed
SQL> !oerr ora 1743
01743, 00000, "only pure functions can be indexed"
// *Cause: The indexed function uses SYSDATE or the user environment.
// *Action: PL/SQL functions must be pure (RNDS, RNPS, WNDS, WNPS). SQL
// expressions must not use SYSDATE, USER, USERENV(), or anything
// else dependent on the session state. NLS-dependent functions
// are OK.
--因为含有sysdate创建函数index失败
SQL> CREATE OR REPLACE FUNCTION f_xifenfei (itime DATE)
2 RETURN DATE
3 IS
4 otime DATE;
5 BEGIN
6 otime:=NVL(itime,SYSDATE);
7 RETURN otime;
8 END;
9 /
Function created.
--想采用自定义函数屏蔽掉sysdate在创建index时候的影响
SQL> create index in_t_xifenfei on t_xifenfei (f_xifenfei(intime)) online nologging;
create index in_t_xifenfei on t_xifenfei (f_xifenfei(intime)) online nologging
*
ERROR at line 1:
ORA-30553: The function is not deterministic
SQL> !oerr ora 30553
30553, 00000, "The function is not deterministic"
// *Cause: The function on which the index is defined is not deterministic
// *Action: If the function is deterministic, mark it DETERMINISTIC. If it
// is not deterministic (it depends on package state, database state,
// current time, or anything other than the function inputs) then
// do not create the index. The values returned by a deterministic
// function should not change even when the function is rewritten or
// recompiled.
--因为函数缺少deterministic不能使用于index上
SQL> CREATE OR REPLACE FUNCTION f_xifenfei (itime DATE)
2 RETURN DATE deterministic
3 IS
4 otime DATE;
5 BEGIN
6 otime:=NVL(itime,SYSDATE);
7 RETURN otime;
8 END;
9 /
Function created.
SQL> create index in_t_xifenfei on t_xifenfei (f_xifenfei(intime)) online nologging;
Index created.
--创建函数index成功
SQL> exec dbms_stats.gather_table_stats(USER,'T_XIFENFEI',cascade => TRUE);
PL/SQL procedure successfully completed.
4.再次查询
确定已经使用函数index，达到在index中使用sysdate函数index的目的。
SQL> set autot on exp stat
SQL> select * from t_xifenfei where f_xifenfei(intime)>=to_date('2011-12-31','yyyy-mm-dd');
Execution Plan
----------------------------------------------------------
Plan hash value: 2005404611
---------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 10 | 200 | 3 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID| T_XIFENFEI | 10 | 200 | 3 (0)| 00:00:01 |
|* 2 | INDEX RANGE SCAN | IN_T_XIFENFEI | 10 | | 2 (0)| 00:00:01 |
---------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("CHF"."F_XIFENFEI"("INTIME")>=TO_DATE(' 2011-12-31 00:00:00',
'syyyy-mm-dd hh24:mi:ss'))
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
5 consistent gets
0 physical reads
0 redo size
770 bytes sent via SQL*Net to client
519 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
9 rows processed
5.总结说明
5.1)通过函数屏蔽函数index的时候，不能使用sysdate
5.2)在创建函数时，需要指定deterministic关键字
