Skip to Main Content

Breadcrumb

Question and Answer

Tom Kyte

Thanks for the question, Denis.

Asked: March 16, 2004 - 10:49 am UTC

Last updated: February 27, 2006 - 7:16 am UTC

Version: 9.2

Viewed 1000+ times

You Asked

Will you explain why in the following script
f_tmp function is called different times in Query1 and Query2.

create or replace function f_tmp(p_ignored number) return number is
l_ignored number;
begin
select count(*) into l_ignored from user_objects;
return 1;
end;
/

create table t_tmp (n number);
insert into t_tmp values (1);
insert into t_tmp values (1);
insert into t_tmp values (2);
insert into t_tmp values (2);
insert into t_tmp values (1);
insert into t_tmp values (1);
insert into t_tmp values (3);
insert into t_tmp values (2);
insert into t_tmp values (2);

select * from t_tmp;

set timing on;
--alter session set sql_trace=true;

Query1

select count(*) from t_tmp t where 1=f_tmp(t.n);

In trace the file I see (function's query):

SELECT count(*) from user_objects

call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 0 0 0
Execute 9 0.00 0.00 0 0 0 0
Fetch 9 0.05 0.04 0 4428 0 9
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 19 0.05 0.05 0 4428 0 9

Query2

select count(*) from t_tmp t where 1=(select f_tmp(t.n) from dual);

The trace file:

SELECT count(*) from user_objects

call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 0 0 0
Execute 3 0.00 0.00 0 0 0 0
Fetch 3 0.02 0.01 0 1476 0 3
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 7 0.02 0.01 0 1476 0 3


--alter session set sql_trace=false;

It looks as if in the first case f_tmp is called for every row
in t_tmp table and in the second case the subquery results
(select f_tmp(t.n) from dual) are cached for every value of the function's
parameter (t.n in the statement).
What's the rule in here?

Thanks.
Denis



and Tom said...

thats an interesting suboptimization.

but the bottom line is -- there are in fact "no rules" governing the order of operation, how many times things are called. It all depends on the "plan". Here you have introduced a correlated scalar subquery and it is doing something "fancy" with it.

the change can be seen using this technique:

ops$tkyte@ORA9IR2> create or replace function f_tmp(p_ignored number) return number
2 is
3 begin
4 dbms_application_info.set_client_info( userenv('client_info')+1 );
5 return 1;
6 end;
7 /

Function created.

using that, we can:

ops$tkyte@ORA9IR2> exec dbms_application_info.set_client_info(0);

PL/SQL procedure successfully completed.

ops$tkyte@ORA9IR2> select count(*) from t_tmp t where 1=f_tmp(t.n);

COUNT(*)
----------
9

ops$tkyte@ORA9IR2> select userenv('client_info') from dual;

USERENV('CLIENT_INFO')
----------------------------------------------------------------
9

that shows our function was called 9 times...


ops$tkyte@ORA9IR2> delete from plan_table;

5 rows deleted.

ops$tkyte@ORA9IR2> explain plan for select count(*) from t_tmp t where 1=f_tmp(t.n);

Explained.

ops$tkyte@ORA9IR2> select * from table(dbms_xplan.display);

PLAN_TABLE_OUTPUT
-----------------------------------------------------------------------------------------------------------------------------------

--------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost |
--------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | |
| 1 | SORT AGGREGATE | | | | |
|* 2 | TABLE ACCESS FULL | T_TMP | | | |
--------------------------------------------------------------------

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

2 - filter("OPS$TKYTE"."F_TMP"("T"."N")=1)

Note: rule based optimization

15 rows selected.

and that the filter was done row by row by row. Now:



ops$tkyte@ORA9IR2> exec dbms_application_info.set_client_info(0);

PL/SQL procedure successfully completed.

ops$tkyte@ORA9IR2> set autotrace on explain
ops$tkyte@ORA9IR2> select count(*) from t_tmp t where 1=(select f_tmp(t.n) from dual);

COUNT(*)
----------
9


Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE
1 0 SORT (AGGREGATE)
2 1 FILTER
3 2 TABLE ACCESS (FULL) OF 'T_TMP'
4 2 TABLE ACCESS (FULL) OF 'DUAL'



ops$tkyte@ORA9IR2> set autotrace off
ops$tkyte@ORA9IR2> select userenv('client_info') from dual;

USERENV('CLIENT_INFO')
----------------------------------------------------------------
3

that shows it was called three times -- once each for 1,2,3. the plan is very different:

ops$tkyte@ORA9IR2>
ops$tkyte@ORA9IR2> delete from plan_table;

3 rows deleted.

ops$tkyte@ORA9IR2> explain plan for select count(*) from t_tmp t where 1=(select f_tmp(t.n) from dual);

Explained.

ops$tkyte@ORA9IR2> select * from table(dbms_xplan.display);

PLAN_TABLE_OUTPUT
-----------------------------------------------------------------------------------------------------------------------------------

--------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost |
--------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | |
| 1 | SORT AGGREGATE | | | | |
|* 2 | FILTER | | | | |
| 3 | TABLE ACCESS FULL | T_TMP | | | |
| 4 | TABLE ACCESS FULL | DUAL | | | |
--------------------------------------------------------------------

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

2 - filter( (SELECT "OPS$TKYTE"."F_TMP"(:B1) FROM "SYS"."DUAL"
"DUAL")=1)

Note: rule based optimization

18 rows selected.

the filter was happening at a much higher level. However, this suboptimization doesn't "scale" if you will -- consider:


ops$tkyte@ORA9IR2> insert into t_tmp select object_id from all_objects;

30601 rows created.

ops$tkyte@ORA9IR2> insert into t_tmp select object_id from all_objects;

30601 rows created.

ops$tkyte@ORA9IR2> insert into t_tmp select object_id from all_objects;

30601 rows created.

ops$tkyte@ORA9IR2>
ops$tkyte@ORA9IR2> exec dbms_application_info.set_client_info(0);

PL/SQL procedure successfully completed.

ops$tkyte@ORA9IR2> select count(*) from t_tmp t where 1=(select f_tmp(t.n) from dual);

COUNT(*)
----------
91812

ops$tkyte@ORA9IR2> set autotrace off
ops$tkyte@ORA9IR2> select userenv('client_info') from dual;

USERENV('CLIENT_INFO')
----------------------------------------------------------------
91296

they skipped calling it a couple of times -- but there was just too many values to do whatever caching they were doing and it is sort of "obviated" here. They called the function virtually once per row in this case


But basically -- you *cannot* rely on a function getting called once per row, once per unique value -- whatever. Consider:

ops$tkyte@ORA9IR2> create table t as select * from t_tmp order by n;

Table created.

ops$tkyte@ORA9IR2> exec dbms_application_info.set_client_info(0);

PL/SQL procedure successfully completed.

ops$tkyte@ORA9IR2> select count(*) from t where 1=(select f_tmp(t.n) from dual);

COUNT(*)
----------
91812

ops$tkyte@ORA9IR2> set autotrace off
ops$tkyte@ORA9IR2> select userenv('client_info') from dual;

USERENV('CLIENT_INFO')
----------------------------------------------------------------
30603

the order of the data was really relevant here. they must be caching the last few filter values and reusing them -- due to the filter step being "higher up".

Interesting...




Rating

  (4 ratings)

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

Comments

caching the last value

Alberto Dell'Era, March 16, 2004 - 6:12 pm UTC 

I think they are caching the last value, reusing it until "n" changes (i'm on 9.2.0.4):

Only one distinct value:

SQL> insert into t_tmp select 1 from all_objects where rownum <= 100;

100 rows created.

SQL> exec dbms_application_info.set_client_info(0);

PL/SQL procedure successfully completed.

SQL> select count(*) from t_tmp t where 1=(select f_tmp(t.n) from dual);

  COUNT(*)
----------
       100

SQL>  select userenv('client_info') from dual;

USERENV('CLIENT_INFO')
-------------------------------------------------------------------------
1

Two distinct values:

SQL> delete from t_tmp;

100 rows deleted.

SQL> insert into t_tmp select 1  from all_objects where rownum <= 50;

50 rows created.

SQL> insert into t_tmp select 2  from all_objects where rownum <= 50;

50 rows created.

SQL>  exec dbms_application_info.set_client_info(0);

PL/SQL procedure successfully completed.

SQL> select count(*) from t_tmp t where 1=(select f_tmp(t.n) from dual);

  COUNT(*)
----------
       100

SQL> select userenv('client_info') from dual;

USERENV('CLIENT_INFO')
--------------------------------------------------------------------------------
2

Three distinct values:

SQL> delete from t_tmp;

100 rows deleted.

SQL> insert into t_tmp select 1  from all_objects where rownum <= 33;

33 rows created.

SQL> insert into t_tmp select 2  from all_objects where rownum <= 33;

33 rows created.

SQL> insert into t_tmp select 3  from all_objects where rownum <= 34;

34 rows created.

SQL>  exec dbms_application_info.set_client_info(0);

PL/SQL procedure successfully completed.

SQL>  select count(*) from t_tmp t where 1=(select f_tmp(t.n) from dual);

  COUNT(*)
----------
       100

SQL>  select userenv('client_info') from dual;

USERENV('CLIENT_INFO')
----------------------------------------------------------------------------
3


Eleven distinct values:

SQL> delete from t_tmp;

100 rows deleted.

SQL> insert into t_tmp select trunc (rownum / 10) from all_objects where rownum <= 100;

100 rows created.

SQL> select count (distinct n) from t_tmp;

COUNT(DISTINCTN)
----------------
              11

NB: count (distinct) is equal to the number of "changes" of n ONLY IN THIS CASE, since we know that they will full-scan, fetching the rows in the same order we inserted them (but only in this "dust clean" experiment). 

SQL>  exec dbms_application_info.set_client_info(0);

PL/SQL procedure successfully completed.

SQL> select count(*) from t_tmp t where 1=(select f_tmp(t.n) from dual);

  COUNT(*)
----------
       100

SQL> select userenv('client_info') from dual;

USERENV('CLIENT_INFO')
--------------------------------------------------------------------------------
11

bye
Alberto

essentially improves performance

Denis Kolesnikov, March 17, 2004 - 6:52 am UTC

Tom, thank you for the exhaustive answer.
I use this feature in my FGAC-like views and that essentially improves performance.

Validate dates using user-defined functions

Narendra, February 27, 2006 - 5:08 am UTC

Tom,

If it can not be relaibly determine how many times a function is called in a SQL, how do I handle following situation:
I want to validate a column defined as VARCHAR2 but containing a DATE value and log all rows having invalid date in a table. If I use TO_DATE, it goes in exception at first occurence. I am forced to use a PL/SQL function that will do this validation and then call the function in a SQL to add erroneous records to log table.

Thanks

Tom Kyte
February 27, 2006 - 7:16 am UTC

I don't follow you here. Not sure what you are asking/saying.


"I want to validate a column defined as a varchar2" - fine.

"but containing a date value" (apparently not, it SOMETIMES has text in it that can be intrpreted as a date, sometimes not)

"If I use to_date, it goes in exception as first (bad?) occurrence" - correct ? so?



I think you might be asking "how do I validate this information in a loop"


for x in ( select * from table_with_data )
loop
begin
l_date := to_date( x.string_that_might_have_date );
l_date_ok := true;
exception
when others then l_date_ok := FALSE;
end if;

.... process, look at boolean ....
end loop;

for example


Exactly

Narendra, February 27, 2006 - 7:38 am UTC

Tom,

You are bang on target.
for x in ( select * from table_with_data )
loop
begin
l_date := to_date( x.string_that_might_have_date );
l_date_ok := true;
exception
when others then l_date_ok := FALSE;
end if;

IF l_date_ok THEN
-- Add to log table.
END IF;
end loop;

Adding to log table will be deferred and be done after all rows are validated.

Thanks

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 latest video and Chris's latest video 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