deterministic

SQL> select /*+ no_result_cache */ count(*) from test where exists(select * from test2 where test2.deptno = test.deptno);  COUNT(*)----------13已用时间:  00: 00: 00.01SQL> select * from table(dbms_xplan.display_cursor(null,0,format=>'ALL ALLSTATS LAST NOTE'));PLAN_TABLE_OUTPUT------------------------------------------------------SQL_ID4p295pdp9n14x, child number 0-------------------------------------select /*+ no_result_cache */ count(*) from test where exists(select *from test2 where test2.deptno = test.deptno)Plan hash value: 3330529533--------------------------------------------------------------------------------------------------------------------------------------------------| Id  | Operation    | Name  | Starts | E-Rows |E-Bytes| Cost (%CPU)| E-Time   | A-Rows |   A-Time   | Buffers |  OMem |  1Mem | Used-Mem |--------------------------------------------------------------------------------------------------------------------------------------------------|   0 | SELECT STATEMENT    |    |   1 |      |       |     7 (100)|      |      1 |00:00:00.01 |    6 |       |       |  ||   1 |  SORT AGGREGATE     |    |   1 |    1 |    26 |    |      |      1 |00:00:00.01 |    6 |       |       |  ||*  2 |   HASH JOIN SEMI    |    |   1 |   13 |   338 |     7  (15)| 00:00:01 |     13 |00:00:00.01 |    6 |  1517K|  1517K|  670K (0)||   3 |    TABLE ACCESS FULL| TEST  |   1 |   14 |   182 |     3(0)| 00:00:01 |     14 |00:00:00.01 |    3 |       |       |  ||   4 |    TABLE ACCESS FULL| TEST2 |   1 |    4 |    52 |     3(0)| 00:00:01 |      3 |00:00:00.01 |    3 |       |       |  |--------------------------------------------------------------------------------------------------------------------------------------------------SQL> create or replace function has_dept(v_deptno test2.deptno%type) return number as  2  v_count int;  3  begin  4    select 1 into v_count from test2 where test2.deptno = v_deptno;  5    return nvl(v_count,-1);  6  end;  7  / Function createdSQL> alter system flush shared_pool; System alteredSQL> select /*+ no_result_cache */ count(*) from test where has_dept(test.deptno)=1;   COUNT(*)----------        13SQL> select v.SQL_TEXT,v.EXECUTIONS from v$sql v where v.SQL_TEXT like '%DEPT%' and v.sql_text not like '%v$sql%'; SQL_TEXT                                                                         EXECUTIONS-------------------------------------------------------------------------------- ----------SELECT 1 FROM TEST2 WHERE TEST2.DEPTNO = :B1                                             14SQL> alter system flush shared_pool; System alteredSQL> select v.SQL_TEXT,v.EXECUTIONS from v$sql v where v.SQL_TEXT like '%DEPT%' and v.sql_text not like '%v$sql%'; SQL_TEXT                                                                         EXECUTIONS-------------------------------------------------------------------------------- ----------SQL> create or replace function has_dept(v_deptno test2.deptno%type) return number deterministic as  2  v_count int;  3  begin  4    select 1 into v_count from test2 where test2.deptno = v_deptno;  5    return nvl(v_count,-1);  6  end;  7  / Function createdSQL> select /*+ no_result_cache */ count(*) from test where has_dept(test.deptno)=1;   COUNT(*)----------        13SQL> select v.SQL_TEXT,v.EXECUTIONS from v$sql v where v.SQL_TEXT like '%DEPT%' and v.sql_text not like '%v$sql%'; /*注：此处有时查到为5，因对trace不感兴趣，未作深入研究*/SQL_TEXT                                                                         EXECUTIONS-------------------------------------------------------------------------------- ----------SELECT 1 FROM TEST2 WHERE TEST2.DEPTNO = :B1                                              4SQL> select /*+ no_result_cache */ count(*) from test where has_dept(test.deptno)=1;   COUNT(*)----------        13SQL> update test2 set deptno = deptno + 100; 4 rows updatedSQL> select /*+ no_result_cache */ count(*) from test where has_dept(test.deptno)=1;   COUNT(*)----------         0 SQL>