Re: Function in a WHERE clause

Shabba <shabba_at_nowhere.com> wrote in message news:37CE91BE.63FCCDF_at_nowhere.com...
> Can anyone give a definitive answer to the following?
>
> In PL/SQL, a row has to be selected from the database on the condition
> that a key value matches a parameter which is first converted by another
> function. Is there any advantage/disadvantage in doing the conversion
> before the select rather than in the WHERE clause of the SELECT?
>
> i.e. which (if either) of the following code segments would be better?
>
> a)
>
> SELECT rowid
> FROM any_table
> WHERE key_value = my_func(a_parameter);
>
> b)
>
> a_local_variable := my_func(a_parameter)
>
> SELECT rowid
> FROM any_table
> WHERE key_value = a_local_variable
>
> If anyone can let me know which is better, with a sensible reason why,
> I'd be grateful.
>
> Thanks
>
> Sh.
>
> PS This is on Oracle 7.3 for SunOS if it makes a difference.

(B) is better, since it performs the function only once obviously, but (A) performs the function once for each row.

The following script shows the proof.
I use a trick to count the times Oracle executes the function: access a specific table inside the function, and audit activity on the table. (I don't directly audit on the function, it will lose some audit trail records.) Every time Oracle execute the function, it is recorded in the audit trail.

• begin script SQL> create table test(n number);

Table created.

SQL> insert into test values (20);

1 row created.

SQL> commit;

Commit complete.

SQL> create or replace function my_fun return number   2 as
  3 n number;
  4 begin
  5 select n into n from test;
  6 return 20;
  7 end;
  8 /

Function created.

SQL> truncate table sys.aud$;

Table truncated.

SQL> audit select on test by access;

Audit succeeded.

SQL> variable my_var number;
SQL> exec :my_var:=my_fun;

PL/SQL procedure successfully completed.

SQL> select * from emp where deptno=:my_var;

    EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO

--------- ---------- --------- --------- --------- --------- --------- ---------
     7369 SMITH      CLERK          7902 17-DEC-80       800                  20
     7566 JONES      MANAGER        7839 02-APR-81      2975                  20
     7788 SCOTT      ANALYST        7566 19-APR-87      3000                  20
     7876 ADAMS      CLERK          7788 23-MAY-87      1100                  20
     7902 FORD       ANALYST        7566 03-DEC-81      3000                  20

SQL> select count(*) from user_audit_trail   2 where obj_name='TEST' and action_name='SELECT';

 COUNT(*)

        1

SQL> select * from emp where deptno=my_fun;

    EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO

--------- ---------- --------- --------- --------- --------- --------- ---------
     7369 SMITH      CLERK          7902 17-DEC-80       800                  20
     7566 JONES      MANAGER        7839 02-APR-81      2975                  20
     7788 SCOTT      ANALYST        7566 19-APR-87      3000                  20
     7876 ADAMS      CLERK          7788 23-MAY-87      1100                  20
     7902 FORD       ANALYST        7566 03-DEC-81      3000                  20

SQL> select count(*) from user_audit_trail   2 where obj_name='TEST' and action_name='SELECT';

 COUNT(*)

       15

SQL> select * from emp where deptno=(select my_fun from dual);

    EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO

--------- ---------- --------- --------- --------- --------- --------- ---------
     7369 SMITH      CLERK          7902 17-DEC-80       800                  20
     7566 JONES      MANAGER        7839 02-APR-81      2975                  20
     7788 SCOTT      ANALYST        7566 19-APR-87      3000                  20
     7876 ADAMS      CLERK          7788 23-MAY-87      1100                  20
     7902 FORD       ANALYST        7566 03-DEC-81      3000                  20

SQL> select count(*) from user_audit_trail   2 where obj_name='TEST' and action_name='SELECT';

 COUNT(*)

       16
-- end script

"select * from emp where deptno=my_fun" executes the function 14 times, even if the deptno column is indexed.

As what Thomas Kyte said, "select * from emp where deptno=(select my_fun from dual)" executes the function only once. Received on Fri Sep 03 1999 - 15:04:40 CDT