sitemap

RSS地图

收藏本站

设为首页

Oracle研究中心

当前位置:Oracle研究中心 > 开发DBA >

【学习笔记】Oracle索引 创建含sysdate的函数index案例

时间:2016-07-01 23:02   来源:Oracle研究中心   作者:惜分飞   点击:

天萃荷净 分享一篇运维DBA需求,创建含sysdate的函数index案例

1.模拟Oracle数据库环境

创建表插入数据库

[oracle@node1 ~]$ sqlplus chf/oracleplus

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_oracleplus(id number,intime date);

Table created.

SQL> DECLARE
  2  i NUMBER;
  3  BEGIN
  4    FOR i IN 1..1000 LOOP
  5      INSERT INTO t_oracleplus VALUES(i,SYSDATE-i);
  6      END LOOP;
  7      COMMIT;
  8  END;
  9  /

PL/SQL procedure successfully completed.

SQL> select count(*) from t_oracleplus;

  COUNT(*)
----------
      1000


SQL> exec dbms_stats.gather_table_stats(USER,'T_oracleplus',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_oracleplus |    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_oracleplus on t_oracleplus (nvl(intime,sysdate)) online nologging;
 create index in_t_oracleplus on t_oracleplus (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_oracleplus (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_oracleplus on t_oracleplus (f_oracleplus(intime)) online nologging;
 create index in_t_oracleplus on t_oracleplus (f_oracleplus(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_oracleplus (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_oracleplus on t_oracleplus (f_oracleplus(intime)) online nologging;

Index created.
--创建函数index成功

SQL> exec dbms_stats.gather_table_stats(USER,'T_oracleplus',cascade => TRUE);

PL/SQL procedure successfully completed.

4.再次查询
确定已经使用函数index,达到在index中使用sysdate函数index的目的。

SQL> set autot on  exp stat
SQL> select * from t_oracleplus where f_oracleplus(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_oracleplus    |    10 |   200 |     3   (0)| 00:00:01 |

|*  2 |   INDEX RANGE SCAN          | IN_T_oracleplus |    10 |       |     2   (0)| 00:00:01 |

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


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

   2 - access("CHF"."F_oracleplus"("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关键字


--------------------------------------ORACLE-DBA----------------------------------------

最权威、专业的Oracle案例资源汇总之【学习笔记】Oracle索引 创建含sysdate的函数index案例

本文由大师惜分飞原创分享,网址:http://www.oracleplus.net/arch/680.html

Oracle研究中心

关键词:

Oracle索引

创建含sysdate的函数index案例