How many function calls and sysdate evaluated

Talking here about function calls and when sysdate is evaluated. Starting with a silly function f2 that takes a second to execute and a session that shows only the seconds out of a date type.

SQL> alter session set nls_date_format='ss';

Session altered.

SQL> create or replace function f2( x in date ) return date
  2    as
  3    begin
  4            dbms_lock.sleep(1);
  5            return x;
  6    end;
  7  /

Function created.

SQL> select a a, a b from (select f2(sysdate) a from dual);

A  B
-- --
37 37

Elapsed: 00:00:02.02

Written a query that uses the function once. And seems like sysdate is evaluated at the main query and it returns the same value for both a and b. But how come the execution time is two seconds? From the optimizer trace can be seen that the query is transformed to have two calls to the function. That explains the execution time.
Final query after transformations:******* UNPARSED QUERY IS *******
SELECT "RAFU"."F2"() "A","RAFU"."F2"() "B" FROM "SYS"."DUAL" "DUAL"
SQL> select f2(sysdate) a, f2(sysdate) b from dual;

A  B
-- --
39 39

Elapsed: 00:00:02.02

Similar result. Lets change the function to have its own cursor.

SQL> create or replace function f3 return date
  2    as
  3    x date;
  4    begin
  5            dbms_lock.sleep(1);
  6            select sysdate into x from dual;
  7            return x;
  8    end;
  9  /

Function created.

SQL> select a a, a b from (select f3() a from dual);

A  B
-- --
42 43

Elapsed: 00:00:02.02

Execution time the same and the result changed. The sysdate is evaluated at the time cursor in the function is called. Remember the query transformation. The transformation may be avoided and the function is called actually only once.

SQL> select a a, a b from (select /*+no_merge*/ f3() a from dual);

A  B
-- --
54 54

Elapsed: 00:00:03.01

Be aware query transformations may lead to changing query results when function are mixed in a query. How about transaction isolation. Does that have influence on the issue when the function is called twice. So is the sysdate behaving like changing rownum. The changing rownum is talked in Laurent Schneider blog and comments there.

SQL> set transaction read only;

Transaction set.

Elapsed: 00:00:00.00

A  B
-- --
03 04

Elapsed: 00:00:02.01
SQL> rollback;
The sysdate is not respecting the read only setting on the transaction but it is getting its value just when asked.

No comments:

Post a Comment

About Me

My photo
I am Timo Raitalaakso. I have been working since 2001 at Solita Oy as a Senior Database Specialist. I have received Oracle ACE nomination. My main focus is on projects involving Oracle database. In this Rafu on db blog I write some interesting issues that evolves from my interaction with databases. Mainly Oracle.