Function calls count during SQL execution.

From: <J.Velikanovs_at_alise.lv>
Date: Mon, 21 Jun 2004 19:14:36 +0300
Message-ID: <OFF778A20D.612FBAB7-ONC2256EBA.005921AD-C2256EBA.0059E0A8@alise.lv>

Hello any one,
How many times Oracle executes user function during SQL execution, I wonder (if there any related docs. or papers please point me)? Take a look on TESTCASE below. It is strange to me, that Oracle with index on mthe column execute function 2 times, but without for each row. Why it so?
Is there any method to tell Oracle to execute user function (testsleep) ones?

!!! not relevant part !!!

SYS:jozh> show release
release 902000400
SYS:jozh> drop table teststab;
Table dropped.
SYS:jozh> create table teststab (n number); Table created.
SYS:jozh> begin for f in 1..5 loop insert into teststab values(f); end loop; commit; end;
SYS:jozh> /
PL/SQL procedure successfully completed. SYS:jozh> create index teststabi1 on teststab (n); Index created.

SYS:jozh> create or replace function testsleep return number is
SYS:jozh> begin dbms_lock.sleep(3);
SYS:jozh> return 1; end;
SYS:jozh> /

Function created.
SYS:jozh> set timing on

!!! relevant part !!!

SYS:jozh> select * from teststab where n=testsleep;

1
Elapsed: 00:00:06.15
SYS:jozh> drop index teststabi1;
Index dropped.
SYS:jozh> select * from teststab where n=testsleep;

1
Elapsed: 00:00:15.38
SYS:jozh> set timing off

Jurijs

PS
FULL TESTCASE TEXT:
show release
drop table teststab;
create table teststab (n number);
begin for f in 1..5 loop insert into teststab values(f); end loop; commit; end;
/
create index teststabi1 on teststab (n);

create or replace function testsleep return number is begin dbms_lock.sleep(3);
return 1; end;
/

set timing on
select * from teststab where n=testsleep; drop index teststabi1;
select * from teststab where n=testsleep; set timing off

Please see the official ORACLE-L FAQ: http://www.orafaq.com

To unsubscribe send email to: oracle-l-request_at_freelists.org put 'unsubscribe' in the subject line.

--
Archives are at http://www.freelists.org/archives/oracle-l/
FAQ is at http://www.freelists.org/help/fom-serve/cache/1.html
-----------------------------------------------------------------

Received on Mon Jun 21 2004 - 11:18:48 CDT