Skip to Main Content
  • Questions

  • where clause function called, has index and no index,function called time difference

Breadcrumb

Question and Answer

Tom Kyte

Thanks for the question.

Asked: April 29, 2009 - 11:38 am UTC

Last updated: April 29, 2009 - 12:28 pm UTC

Version: 10.2.0

Viewed 1000+ times

You Asked

hi,dear tom,i found a question that in when function is called in where clause,
if clause hasn't an index,the functin is called times is equal to items that selected items,but when has an index,the functin just is called two times.this is why. i had attempted to find answer in old questions,but not found,maybe my
search way has some mistakes.can you help me?

what i do is following:
SQL>CREATE TABLE TEST(USERNAME VARCHAR2(30));

SQL>INSERT INTO TEST SELECT USERNAME FROM ALL_USERS WHERE ROWNUM <= 10;

SQL>SELECT ROWNUM AS NUM,USERNAME FROM TEST;
NUM USERNAME
---------- ------------------------------
1 LEEINNEU_D
2 PERFSTAT
3 OPS$LEEINNEU
4 LEEINNEU
5 SCOTT
6 MGMT_VIEW
7 MDDATA
8 SYSMAN
9 MDSYS
10 SI_INFORMTN_SCHEMA

SQL>CREATE OR REPLACE FUNCTION FUN_GET_USER_NAME(i_name IN VARCHAR2)
RETURN VARCHAR2 AS
BEGIN
dbms_tput.put_line('INNER FUNCTION CALLED: ' || i_name);
RETURN i_name;
END FUN_GET_USER_NAME;

SQL>CREATE OR REPLACE PROCEDURE PRO_TEST AS
n_count NUMBER;
BEGIN
dbms_output.put_line('---------------PROCEDURE START--------------');

SELECT COUNT(*)
INTO n_count
FROM TEST T
WHERE T.USERNAME = FUN_GET_USER_NAME('LEEINNEU');

dbms_output.put_line('---------------PROCEDURE END--------------');
dbms_output.put_line('COUNT:' || n_count);
EXCEPTION
WHEN OTHERS THEN
dbms_output.put_line(SQLERRM);
END PRO_TEST;

SQL>EXEC PRO_TEST;
---------------PROCEDURE START--------------
INNER FUNCTION CALLED: LEEINNEU
INNER FUNCTION CALLED: LEEINNEU
INNER FUNCTION CALLED: LEEINNEU
INNER FUNCTION CALLED: LEEINNEU
INNER FUNCTION CALLED: LEEINNEU
INNER FUNCTION CALLED: LEEINNEU
INNER FUNCTION CALLED: LEEINNEU
INNER FUNCTION CALLED: LEEINNEU
INNER FUNCTION CALLED: LEEINNEU
INNER FUNCTION CALLED: LEEINNEU
---------------PROCEDURE END--------------
COUNT:1

SQL>SELECT COUNT(*) FROM TEST T WHERE T.USERNAME = (SELECT (FUN_GET_USER_NAME('LEEINNEU') FROM DUAL));
---------------------------------------------
INNER FUNCTION CALLED: LEEINNEU
---------------------------------------------

SQL>CREATE INDEX TEST_D1 ON TEST(USERNAME);
SQL>EXEC PRO_TEST;
---------------PROCEDURE START--------------
INNER FUNCTION CALLED: LEEINNEU
INNER FUNCTION CALLED: LEEINNEU
---------------PROCEDURE END--------------
COUNT:1

SQL>SELECT COUNT(*) FROM TEST T WHERE T.USERNAME = (SELECT (FUN_GET_USER_NAME('LEEINNEU') FROM DUAL));
---------------------------------------------
INNER FUNCTION CALLED: LEEINNEU
---------------------------------------------

when has an index,just called tow times,why?

and Tom said...

I absolutely HATE your code, I know it is a demo, but things you do in demo's you probably do in real life:

      EXCEPTION
          WHEN OTHERS THEN
          dbms_output.put_line(SQLERRM);
  END PRO_TEST;


http://asktom.oracle.com/pls/ask/search?p_string=%22i+hate+your+code%22

that is the least professional thing you can code, it makes you look really bad.


the number of times a function is called when embedded in a sql statement can vary widely - drastically - and dramatically. You have no control over how many times it will be called. You have no control over what order they will be called in.


The short answer to your question however is

select * from t where unindexed_column = f(x);

will in general cause f(x) to be evaluated for each and every row in the table T. Why? because it needs to evaluate unindexed_column = f(x) for each row.

select * from t where indexed_column = f(x);

will in general cause f(x) to be evaluated twice for the entire query. Why? Because we will use an index range scan and in order to do an index range scan we need a "start" and "stop" key - and that is all, we get the start/stop keys by running f(x).


The scalar subquery you used (select f(x) from dual) is ALWAYS a good idea when calling plsql from sql, there is a feature called scalar subquery caching

http://asktom.oracle.com/pls/ask/search?p_string=%22scalar+subquery+caching%22

that can dramatically reduce the number of times your function was called. Lets look at that in more detail. (in the following, stage is a copy of all_objects)

ops$tkyte%ORA11GR1> create or replace function f( x in varchar2 ) return number
  2  as
  3  begin
  4          dbms_application_info.set_client_info(userenv('client_info')+1 );
  5          return length(x);
  6  end;
  7  /

Function created.


that function counts how often it was called and stores the result in V$SESSION

ops$tkyte%ORA11GR1> exec :cpu := dbms_utility.get_cpu_time; dbms_application_info.set_client_info(0);

PL/SQL procedure successfully completed.

ops$tkyte%ORA11GR1> set autotrace traceonly statistics;
ops$tkyte%ORA11GR1> select owner, f(owner) from stage;

68464 rows selected.


Statistics
----------------------------------------------------------
        316  recursive calls
          0  db block gets
       5599  consistent gets
       1022  physical reads
          0  redo size
    1295486  bytes sent via SQL*Net to client
      50624  bytes received via SQL*Net from client
       4566  SQL*Net roundtrips to/from client
          5  sorts (memory)
          0  sorts (disk)
      68464  rows processed

ops$tkyte%ORA11GR1> set autotrace off
ops$tkyte%ORA11GR1> select dbms_utility.get_cpu_time-:cpu cpu_hsecs, userenv('client_info') from dual;

 CPU_HSECS USERENV('CLIENT_INFO')
---------- ----------------------------------------------------------------
       205 68464


Ok, that took 2 cpu seconds and called our function ONCE per row in the table. If we simply wrap it in a scalar subquery:

ops$tkyte%ORA11GR1> exec :cpu := dbms_utility.get_cpu_time; dbms_application_info.set_client_info(0);

PL/SQL procedure successfully completed.

ops$tkyte%ORA11GR1> set autotrace traceonly statistics;
ops$tkyte%ORA11GR1> select owner, (select f(owner) from dual) f from stage;

68464 rows selected.


Statistics
----------------------------------------------------------
          1  recursive calls
          0  db block gets
       5511  consistent gets
       1009  physical reads
          0  redo size
    1295479  bytes sent via SQL*Net to client
      50624  bytes received via SQL*Net from client
       4566  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
      68464  rows processed

ops$tkyte%ORA11GR1> set autotrace off
ops$tkyte%ORA11GR1> select dbms_utility.get_cpu_time-:cpu cpu_hsecs, userenv('client_info') from dual;

 CPU_HSECS USERENV('CLIENT_INFO')
---------- ----------------------------------------------------------------
        29 73


It called it only 73 times (due to the scalar subquery cache!) Now, in my case - it (the function) was called more often than it needed to be - due to hash collisions in the subquery cache (we could not cache *every thing*). If we were to pre-sort the data and then invoke the function against the sorted data (sort by the inputs to the function) we can achieve the fewest invocations:

ops$tkyte%ORA11GR1> exec :cpu := dbms_utility.get_cpu_time; dbms_application_info.set_client_info(0);

PL/SQL procedure successfully completed.

ops$tkyte%ORA11GR1> set autotrace traceonly statistics;
ops$tkyte%ORA11GR1> select owner, (select f(owner) from dual) f
  2    from (select owner, rownum r from stage order by owner);

68464 rows selected.


Statistics
----------------------------------------------------------
          1  recursive calls
          0  db block gets
       1011  consistent gets
       1008  physical reads
          0  redo size
     922778  bytes sent via SQL*Net to client
      50624  bytes received via SQL*Net from client
       4566  SQL*Net roundtrips to/from client
          1  sorts (memory)
          0  sorts (disk)
      68464  rows processed

ops$tkyte%ORA11GR1> set autotrace off
ops$tkyte%ORA11GR1> select dbms_utility.get_cpu_time-:cpu cpu_hsecs, userenv('client_info') from dual;

 CPU_HSECS USERENV('CLIENT_INFO')
---------- ----------------------------------------------------------------
        46 37


that would only make sense if the function was really expensive to invoke (eg: in this case, the time to sort the data was more than the amount of cpu saved by invoking the function fewer times - so it would not make sense in THIS case)

Now, starting in 10g, the keyword deterministic was picked up by the sql engine and the sql engine would transparently start caching things:

ops$tkyte%ORA11GR1> create or replace function f( x in varchar2 ) return number
  2  <b>DETERMINISTIC</b>
  3  as
  4  begin
  5          dbms_application_info.set_client_info(userenv('client_info')+1 );
  6          return length(x);
  7  end;
  8  /

Function created.

ops$tkyte%ORA11GR1> exec :cpu := dbms_utility.get_cpu_time; dbms_application_info.set_client_info(0);

PL/SQL procedure successfully completed.

ops$tkyte%ORA11GR1> set autotrace traceonly statistics;
ops$tkyte%ORA11GR1> select owner, f(owner) from stage;

68464 rows selected.


Statistics
----------------------------------------------------------
         17  recursive calls
          0  db block gets
       5535  consistent gets
       1008  physical reads
          0  redo size
    1295486  bytes sent via SQL*Net to client
      50624  bytes received via SQL*Net from client
       4566  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
      68464  rows processed

ops$tkyte%ORA11GR1> set autotrace off
ops$tkyte%ORA11GR1> select dbms_utility.get_cpu_time-:cpu cpu_hsecs, userenv('client_info') from dual;

 CPU_HSECS USERENV('CLIENT_INFO')
---------- ----------------------------------------------------------------
       119 7951


so, that shows that deterministic can be beneficial - especially if you cannot rewrite the SQL to use a scalar subquery - but you still want to use the scalar subquery cache!

In 11g, they added "function result caches" - whereby your plsql function would not be executed every time it was called - the value would be saved SERVER WIDE (across sql statements, across sessions, across everything). This will reduce the number of times your plsql function code RUNS:


ops$tkyte%ORA11GR1> create or replace function f( x in varchar2 ) return number
  2  <b>RESULT_CACHE</b>
  3  as
  4  begin
  5          dbms_application_info.set_client_info(userenv('client_info')+1 );
  6          return length(x);
  7  end;
  8  /

Function created.

ops$tkyte%ORA11GR1> exec :cpu := dbms_utility.get_cpu_time; dbms_application_info.set_client_info(0);

PL/SQL procedure successfully completed.

ops$tkyte%ORA11GR1> set autotrace traceonly statistics;
ops$tkyte%ORA11GR1> select owner, f(owner) from stage;

68464 rows selected.


Statistics
----------------------------------------------------------
         17  recursive calls
          0  db block gets
       5535  consistent gets
       1008  physical reads
          0  redo size
    1295486  bytes sent via SQL*Net to client
      50624  bytes received via SQL*Net from client
       4566  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
      68464  rows processed

ops$tkyte%ORA11GR1> set autotrace off
ops$tkyte%ORA11GR1> select dbms_utility.get_cpu_time-:cpu cpu_hsecs, userenv('client_info') from dual;

 CPU_HSECS USERENV('CLIENT_INFO')
---------- ----------------------------------------------------------------
       133 37


see - your code ran only 37 times, but - important but - note the CPU. It is not 0.2 as it was above when we used the scalar subquery cache - it is 1.33. That is the overhead of context switching from sql to plsql 68,464 times. So, even though the code did not run - we still had to flip flop between the sql engine and the plsql engine and that takes time

If we re-execute that query:


ops$tkyte%ORA11GR1> exec :cpu := dbms_utility.get_cpu_time; dbms_application_info.set_client_info(0);

PL/SQL procedure successfully completed.

ops$tkyte%ORA11GR1> set autotrace traceonly statistics;
ops$tkyte%ORA11GR1> select owner, f(owner) from stage;

68464 rows selected.


Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
       5511  consistent gets
       1008  physical reads
          0  redo size
    1295486  bytes sent via SQL*Net to client
      50624  bytes received via SQL*Net from client
       4566  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
      68464  rows processed

ops$tkyte%ORA11GR1> set autotrace off
ops$tkyte%ORA11GR1> select dbms_utility.get_cpu_time-:cpu cpu_hsecs, userenv('client_info') from dual;

 CPU_HSECS USERENV('CLIENT_INFO')
---------- ----------------------------------------------------------------
       103 0


we now ran the code ZERO times, but we spent 0.8 cpu seconds context switching between plsql and sql!

bottom line: if you call plsql from sql - use the scalar subquery technique.


Is this answer out of date? If it is, please let us know via a Comment
more

Connor and Chris don't just spend all day on AskTOM. You can also catch regular content via Connor's blog and Chris's blog. Or if video is more your thing, check out Connor's channel and Chris's channel from their Youtube channels. And of course, keep up to date with AskTOM via the official twitter account.

More to Explore

PL/SQL demos

Check out more PL/SQL tutorials on our LiveSQL tool.

PL/SQL docs

PL/SQL reference manual from the Oracle documentation library