This discussion is archived
9 Replies Latest reply: Dec 8, 2012 10:56 AM by JP_1442650 RSS

A function in a subquery is call too many times.

JP_1442650 Newbie
Currently Being Moderated
Dear all,

I'm struggling to understand why a function in a subquery is called too many times.
Let me explain with an example:
create or replace function all_emp (v_deptno in number) 
return varchar2
as

v_all_emp varchar2(2000);

begin

    dbms_output.put_line ('function called');

    for i in (select * from emp where deptno = v_deptno) loop

        v_all_emp := v_all_emp || i.ename || '; ';
    
    end loop;

return v_all_emp;
    
end;
/

-- running just the subquery, calls the function all_emp only 4 times (once for each row in table dept)
select 
    d.deptno,
    d.dname,
    all_emp(d.deptno) f_all_emp
    from dept d;

-- running the whole query, using regexp to split the value of f_all_emp into separate fields, causes that function all_emp is called 28 times, thus 6 times for each row!!
select tmp.*,
regexp_substr(f_all_emp,'[^;]*',1,1) emp1,
regexp_substr(f_all_emp,'[^;]*',1,3) emp2,
regexp_substr(f_all_emp,'[^;]*',1,5) emp3,
regexp_substr(f_all_emp,'[^;]*',1,7) emp4,
regexp_substr(f_all_emp,'[^;]*',1,9) emp5,
regexp_substr(f_all_emp,'[^;]*',1,11) emp6
from
    (select 
    d.deptno,
    d.dname,
    all_emp(d.deptno) f_all_emp
    from dept d) tmp
;
I don't understand why Oracle is calling my function 28 times in this example, 4 times should be sufficient.

Is there a way to force that the subquery is materialized first?

Little background:
Above function / query is of course a simple example.
Actually I have pretty complex function, embedding in a subquery.
The subquery is already slow (2 min to run), but when I want to split the result of the funciton in multiple (approx 20) fields it's over an hour due to above described behaviour.
  • 1. Re: A function in a subquery is call too many times.
    ranit B Expert
    Currently Being Moderated
    /* code removed... i dont have the Emp & Dept tables */

    Edited by: ranit B on Dec 8, 2012 4:20 PM
    -- removed code
  • 2. Re: A function in a subquery is call too many times.
    JP_1442650 Newbie
    Currently Being Moderated
    Sorry, these tables came standard with my Application Express installation (on Oracle 10.2.0.5):
    CREATE TABLE DEPT
    (
      DEPTNO  NUMBER(2),
      DNAME   VARCHAR2(14 BYTE),
      LOC     VARCHAR2(13 BYTE)
    );
    
    CREATE TABLE EMP
    (
      EMPNO     NUMBER(4)                           NOT NULL,
      ENAME     VARCHAR2(10 BYTE),
      JOB       VARCHAR2(9 BYTE),
      MGR       NUMBER(4),
      HIREDATE  DATE,
      SAL       NUMBER(7,2),
      COMM      NUMBER(7,2),
      DEPTNO    NUMBER(2)
    );
    
    
    Insert into EMP
       (EMPNO, ENAME, JOB, HIREDATE, SAL, 
        DEPTNO)
     Values
       (7839, 'KING', 'PRESIDENT', TO_DATE('11/17/1981 00:00:00', 'MM/DD/YYYY HH24:MI:SS'), 5000, 
        10);
    Insert into EMP
       (EMPNO, ENAME, JOB, MGR, HIREDATE, 
        SAL, DEPTNO)
     Values
       (7698, 'BLAKE', 'MANAGER', 7839, TO_DATE('05/01/1981 00:00:00', 'MM/DD/YYYY HH24:MI:SS'), 
        2850, 30);
    Insert into EMP
       (EMPNO, ENAME, JOB, MGR, HIREDATE, 
        SAL, DEPTNO)
     Values
       (7782, 'CLARK', 'MANAGER', 7839, TO_DATE('06/09/1981 00:00:00', 'MM/DD/YYYY HH24:MI:SS'), 
        2450, 10);
    Insert into EMP
       (EMPNO, ENAME, JOB, MGR, HIREDATE, 
        SAL, DEPTNO)
     Values
       (7566, 'JONES', 'MANAGER', 7839, TO_DATE('04/02/1981 00:00:00', 'MM/DD/YYYY HH24:MI:SS'), 
        2975, 20);
    Insert into EMP
       (EMPNO, ENAME, JOB, MGR, HIREDATE, 
        SAL, DEPTNO)
     Values
       (7788, 'SCOTT', 'ANALYST', 7566, TO_DATE('12/09/1982 00:00:00', 'MM/DD/YYYY HH24:MI:SS'), 
        3000, 20);
    Insert into EMP
       (EMPNO, ENAME, JOB, MGR, HIREDATE, 
        SAL, DEPTNO)
     Values
       (7902, 'FORD', 'ANALYST', 7566, TO_DATE('12/03/1981 00:00:00', 'MM/DD/YYYY HH24:MI:SS'), 
        3000, 20);
    Insert into EMP
       (EMPNO, ENAME, JOB, MGR, HIREDATE, 
        SAL, DEPTNO)
     Values
       (7369, 'SMITH', 'CLERK', 7902, TO_DATE('12/17/1980 00:00:00', 'MM/DD/YYYY HH24:MI:SS'), 
        800, 20);
    Insert into EMP
       (EMPNO, ENAME, JOB, MGR, HIREDATE, 
        SAL, COMM, DEPTNO)
     Values
       (7499, 'ALLEN', 'SALESMAN', 7698, TO_DATE('02/20/1981 00:00:00', 'MM/DD/YYYY HH24:MI:SS'), 
        1600, 300, 30);
    Insert into EMP
       (EMPNO, ENAME, JOB, MGR, HIREDATE, 
        SAL, COMM, DEPTNO)
     Values
       (7521, 'WARD', 'SALESMAN', 7698, TO_DATE('02/22/1981 00:00:00', 'MM/DD/YYYY HH24:MI:SS'), 
        1250, 500, 30);
    Insert into EMP
       (EMPNO, ENAME, JOB, MGR, HIREDATE, 
        SAL, COMM, DEPTNO)
     Values
       (7654, 'MARTIN', 'SALESMAN', 7698, TO_DATE('09/28/1981 00:00:00', 'MM/DD/YYYY HH24:MI:SS'), 
        1250, 1400, 30);
    Insert into EMP
       (EMPNO, ENAME, JOB, MGR, HIREDATE, 
        SAL, COMM, DEPTNO)
     Values
       (7844, 'TURNER', 'SALESMAN', 7698, TO_DATE('09/08/1981 00:00:00', 'MM/DD/YYYY HH24:MI:SS'), 
        1500, 0, 30);
    Insert into EMP
       (EMPNO, ENAME, JOB, MGR, HIREDATE, 
        SAL, DEPTNO)
     Values
       (7876, 'ADAMS', 'CLERK', 7788, TO_DATE('01/12/1983 00:00:00', 'MM/DD/YYYY HH24:MI:SS'), 
        1100, 20);
    Insert into EMP
       (EMPNO, ENAME, JOB, MGR, HIREDATE, 
        SAL, DEPTNO)
     Values
       (7900, 'JAMES', 'CLERK', 7698, TO_DATE('12/03/1981 00:00:00', 'MM/DD/YYYY HH24:MI:SS'), 
        950, 30);
    Insert into EMP
       (EMPNO, ENAME, JOB, MGR, HIREDATE, 
        SAL, DEPTNO)
     Values
       (7934, 'MILLER', 'CLERK', 7782, TO_DATE('01/23/1982 00:00:00', 'MM/DD/YYYY HH24:MI:SS'), 
        1300, 10);
    
    
    Insert into DEPT
       (DEPTNO, DNAME, LOC)
     Values
       (10, 'ACCOUNTING', 'NEW YORK');
    Insert into DEPT
       (DEPTNO, DNAME, LOC)
     Values
       (20, 'RESEARCH', 'DALLAS');
    Insert into DEPT
       (DEPTNO, DNAME, LOC)
     Values
       (30, 'SALES', 'CHICAGO');
    Insert into DEPT
       (DEPTNO, DNAME, LOC)
     Values
       (40, 'OPERATIONS', 'BOSTON');
    COMMIT;
  • 3. Re: A function in a subquery is call too many times.
    ranit B Expert
    Currently Being Moderated
    Thanks a lot for providing the Scripts...

    Let me check.. there's something else...

    Refer this -- http://www.toadworld.com/KNOWLEDGE/KnowledgeXpertforOracle/tabid/648/TopicID/FRMCL/Default.aspx
    >
    Functions - Call a Function in a FROM Clause
    To call a function from within a FROM clause, you must

    Define the RETURN datatype of the function to be a collection (either a nested table or a VARRAY).
    Make sure that all the other parameters to the function are of mode IN and have SQL datatypes (you cannot call a function with a Boolean argument inside a query).
    embed the call to the function inside the TABLE and CAST operators.

    >

    I guess it is the switching between the SQL and Pl/SQL engines , which is forcing the Inline View in FROM clause to get executed every time and hence printing the DBMS_OUTPUT.
    The query is the SQL but the called function is Pl/SQL...

    Edited by: ranit B on Dec 8, 2012 5:06 PM
  • 4. Re: A function in a subquery is call too many times.
    Solomon Yakobson Guru
    Currently Being Moderated
    Optimizer merges in-line view and query results in:
    select  d.deptno,
            d.dname,
            all_emp(d.deptno) f_all_emp
            regexp_substr(all_emp(d.deptno),'[^;]*',1,1) emp1,
            regexp_substr(all_emp(d.deptno),'[^;]*',1,3) emp2,
            regexp_substr(all_emp(d.deptno),'[^;]*',1,5) emp3,
            regexp_substr(all_emp(d.deptno),'[^;]*',1,7) emp4,
            regexp_substr(all_emp(d.deptno),'[^;]*',1,9) emp5,
            regexp_substr(all_emp(d.deptno),'[^;]*',1,11) emp6
      from  dept d
    /
    That's why function is called 28 times. We can see it from explain plan:
    SQL> explain plan for
      2  select tmp.*,
      3          regexp_substr(f_all_emp,'[^;]*',1,1) emp1,
      4          regexp_substr(f_all_emp,'[^;]*',1,3) emp2,
      5          regexp_substr(f_all_emp,'[^;]*',1,5) emp3,
      6          regexp_substr(f_all_emp,'[^;]*',1,7) emp4,
      7          regexp_substr(f_all_emp,'[^;]*',1,9) emp5,
      8          regexp_substr(f_all_emp,'[^;]*',1,11) emp6
      9    from  (
     10           select  d.deptno,
     11                   d.dname,
     12                   all_emp(d.deptno) f_all_emp
     13             from  dept d
     14          ) tmp
     15  /
    
    Explained.
    
    SQL> @?\rdbms\admin\utlxpls
    
    PLAN_TABLE_OUTPUT
    --------------------------------------------------------------------------
    Plan hash value: 3383998547
    
    --------------------------------------------------------------------------
    | Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
    --------------------------------------------------------------------------
    |   0 | SELECT STATEMENT  |      |     4 |    52 |     3   (0)| 00:00:01 |
    |   1 |  TABLE ACCESS FULL| DEPT |     4 |    52 |     3   (0)| 00:00:01 |
    --------------------------------------------------------------------------
    
    8 rows selected.
    
    SQL>  
    If we use NO_MERGE hint:
    SQL> select  /*+ NO_MERGE(tmp) */ 
      2          tmp.*,
      3          regexp_substr(f_all_emp,'[^;]*',1,1) emp1,
      4          regexp_substr(f_all_emp,'[^;]*',1,3) emp2,
      5          regexp_substr(f_all_emp,'[^;]*',1,5) emp3,
      6          regexp_substr(f_all_emp,'[^;]*',1,7) emp4,
      7          regexp_substr(f_all_emp,'[^;]*',1,9) emp5,
      8          regexp_substr(f_all_emp,'[^;]*',1,11) emp6
      9    from  (
     10           select  d.deptno,
     11                   d.dname,
     12                   all_emp(d.deptno) f_all_emp
     13             from  dept d
     14          ) tmp
     15  /
    
        DEPTNO DNAME          F_ALL_EMP  EMP1       EMP2       EMP3       EMP4       EMP5       EMP6
    ---------- -------------- ---------- ---------- ---------- ---------- ---------- ---------- ----------
            10 ACCOUNTING     CLARK; KIN CLARK       KING       MILLER
                              G; MILLER;
    
    
            20 RESEARCH       SMITH; JON SMITH       JONES      SCOTT      ADAMS      FORD
                              ES; SCOTT;
                               ADAMS; FO
                              RD;
    
            30 SALES          ALLEN; WAR ALLEN       WARD       MARTIN     BLAKE      TURNER     JAMES
                              D; MARTIN;
    
        DEPTNO DNAME          F_ALL_EMP  EMP1       EMP2       EMP3       EMP4       EMP5       EMP6
    ---------- -------------- ---------- ---------- ---------- ---------- ---------- ---------- ----------
                               BLAKE; TU
                              RNER; JAME
                              S;
    
            40 OPERATIONS
    
    function called
    function called
    function called
    function called
    function called
    function called
    SQL> explain plan for
      2  select  /*+ NO_MERGE(tmp) */ 
      3          tmp.*,
      4          regexp_substr(f_all_emp,'[^;]*',1,1) emp1,
      5          regexp_substr(f_all_emp,'[^;]*',1,3) emp2,
      6          regexp_substr(f_all_emp,'[^;]*',1,5) emp3,
      7          regexp_substr(f_all_emp,'[^;]*',1,7) emp4,
      8          regexp_substr(f_all_emp,'[^;]*',1,9) emp5,
      9          regexp_substr(f_all_emp,'[^;]*',1,11) emp6
     10    from  (
     11           select  d.deptno,
     12                   d.dname,
     13                   all_emp(d.deptno) f_all_emp
     14             from  dept d
     15          ) tmp
     16  /
    
    Explained.
    
    SQL> @?\rdbms\admin\utlxpls
    
    PLAN_TABLE_OUTPUT
    ------------------------------------------------------------------------------------------------------
    Plan hash value: 2317111044
    
    ---------------------------------------------------------------------------
    | Id  | Operation          | Name | Rows  | Bytes | Cost (%CPU)| Time     |
    ---------------------------------------------------------------------------
    |   0 | SELECT STATEMENT   |      |     4 |  8096 |     3   (0)| 00:00:01 |
    |   1 |  VIEW              |      |     4 |  8096 |     3   (0)| 00:00:01 |
    |   2 |   TABLE ACCESS FULL| DEPT |     4 |    52 |     3   (0)| 00:00:01 |
    ---------------------------------------------------------------------------
    
    9 rows selected.
    
    SQL> 
    Not sure why function is executed 6 and not 4 times. What we actually want is to materialize in-line view:
    SQL> with tmp as (
      2               select  /*+ materialize */
      3                       d.deptno,
      4                       d.dname,
      5                       all_emp(d.deptno) f_all_emp
      6                 from  dept d
      7              )
      8  select  tmp.*,
      9          regexp_substr(f_all_emp,'[^;]*',1,1) emp1,
     10          regexp_substr(f_all_emp,'[^;]*',1,3) emp2,
     11          regexp_substr(f_all_emp,'[^;]*',1,5) emp3,
     12          regexp_substr(f_all_emp,'[^;]*',1,7) emp4,
     13          regexp_substr(f_all_emp,'[^;]*',1,9) emp5,
     14          regexp_substr(f_all_emp,'[^;]*',1,11) emp6
     15    from  tmp
     16  /
    
        DEPTNO DNAME          F_ALL_EMP  EMP1       EMP2       EMP3       EMP4       EMP5       EMP6
    ---------- -------------- ---------- ---------- ---------- ---------- ---------- ---------- ----------
            10 ACCOUNTING     CLARK; KIN CLARK       KING       MILLER
                              G; MILLER;
    
    
            20 RESEARCH       SMITH; JON SMITH       JONES      SCOTT      ADAMS      FORD
                              ES; SCOTT;
                               ADAMS; FO
                              RD;
    
            30 SALES          ALLEN; WAR ALLEN       WARD       MARTIN     BLAKE      TURNER     JAMES
                              D; MARTIN;
    
        DEPTNO DNAME          F_ALL_EMP  EMP1       EMP2       EMP3       EMP4       EMP5       EMP6
    ---------- -------------- ---------- ---------- ---------- ---------- ---------- ---------- ----------
                               BLAKE; TU
                              RNER; JAME
                              S;
    
            40 OPERATIONS
    
    function called
    function called
    function called
    function called
    SQL> explain plan for
      2  with tmp as (
      3               select  /*+ materialize */
      4                       d.deptno,
      5                       d.dname,
      6                       all_emp(d.deptno) f_all_emp
      7                 from  dept d
      8              )
      9  select  tmp.*,
     10          regexp_substr(f_all_emp,'[^;]*',1,1) emp1,
     11          regexp_substr(f_all_emp,'[^;]*',1,3) emp2,
     12          regexp_substr(f_all_emp,'[^;]*',1,5) emp3,
     13          regexp_substr(f_all_emp,'[^;]*',1,7) emp4,
     14          regexp_substr(f_all_emp,'[^;]*',1,9) emp5,
     15          regexp_substr(f_all_emp,'[^;]*',1,11) emp6
     16    from  tmp
     17  /
    
    Explained.
    
    SQL> @?\rdbms\admin\utlxpls
    
    PLAN_TABLE_OUTPUT
    -----------------------------------------------------------------------------------------------------------------------
    Plan hash value: 634594723
    
    ---------------------------------------------------------------------------------------------------------
    | Id  | Operation                  | Name                       | Rows  | Bytes | Cost (%CPU)| Time     |
    ---------------------------------------------------------------------------------------------------------
    |   0 | SELECT STATEMENT           |                            |     4 |  8096 |     5   (0)| 00:00:01 |
    |   1 |  TEMP TABLE TRANSFORMATION |                            |       |       |            |          |
    |   2 |   LOAD AS SELECT           |                            |       |       |            |          |
    |   3 |    TABLE ACCESS FULL       | DEPT                       |     4 |    52 |     3   (0)| 00:00:01 |
    |   4 |   VIEW                     |                            |     4 |  8096 |     2   (0)| 00:00:01 |
    |   5 |    TABLE ACCESS FULL       | SYS_TEMP_0FD9D6603_20255AE |     4 |    52 |     2   (0)| 00:00:01 |
    
    PLAN_TABLE_OUTPUT
    -----------------------------------------------------------------------------------------------------------------------
    ---------------------------------------------------------------------------------------------------------
    
    12 rows selected.
    
    SQL> 
    However, hint MATERIALIZE is an undocumented hint.

    SY.
  • 5. Re: A function in a subquery is call too many times.
    Peter Gjelstrup Guru
    Currently Being Moderated
    Hi,

    When doing PL/SQL function calls from SQL, then "wrap" the call in a scalar subquery. As a good habbit, do it always - even when you do not find it useful.

    In your case:
    select d.deptno
         , d.dname
         , (select all_emp (d.deptno) from dual) f_all_emp
      from dept d
    Good reading
    http://www.oracle.com/technetwork/issue-archive/2011/11-sep/o51asktom-453438.html


    Regards
    Peter
  • 6. Re: A function in a subquery is call too many times.
    chris227 Guru
    Currently Being Moderated
    Dont, never, rely on how often a function is called from sql.
    SQL is not a procedural language and it is up to the optimizer to decide how to reach the final row set.
    Read more on this on ask Tom.

    Scalar subquery is a good practise, but even then you can not be sure, (think of hash collusions for example)

    However, it is easy to reach your goal by simple sql:
    select
     d.deptno
    ,d.dname
    ,listagg(ename,';') within group (order by ename) ename
    from dept d, emp e
    where
    d.deptno=e.deptno
    group by
     d.deptno
    ,d.dname
    
    DEPTNO     DNAME     ENAME
    10     ACCOUNTING     KING;clark;miller;willi
    20     RESEARCH     SMITH;adams;ford;jones;scott
    30     SALES     ALLEN;WARD;blake;james;martin;turner
  • 7. Re: A function in a subquery is call too many times.
    JP_1442650 Newbie
    Currently Being Moderated
    Everybody thanks a lot for the replies.
    Everything helps me to understand my query.

    @ranit B: thanks for your help, but I don't use my function in the FROM clause.

    @Solomon Y: thanks a lot for the extensive explanations. The NO_MERGE hint works great in my case.

    @Peter G: thanks a lot as well, the scalar subquery is giving the same major performance improvement as above NO_MERGE hint.
    Sorry, I noted that I only could mark one reply as 'Correct'.

    @chris227: OK, I understand better now what the optimiser does.
    But my goal to prevent that my function was called multiple times per row.
    For that I used a simple example with some standard tables.
    My actual function can't be replaced with listagg (further I'm on oracle 10.2, which doesn't have this function)
  • 8. Re: A function in a subquery is call too many times.
    chris227 Guru
    Currently Being Moderated
    Yeah, sorry, little misread from my side.
    But again, with the preferable (to the hint) scalar subquery you reduce the number of calls, and in most cases it might be the desired number, but you cant rely on it, for example to increment some counter. It would be relying on a side effect.

    A sql-possibilityfor the sample in 10.x might be
    with depts as (
      select
        row_number() over (partition by d.deptno order by ename) rn
       ,d.deptno
       ,d.dname
       ,ename
      from dept d, emp e
      where
       d.deptno=e.deptno  
    )
    
    select
     deptno
    ,dname
    ,max(decode(rn,1,ename,null)) emp1
    ,max(decode(rn,2,ename,null)) emp2
    ,max(decode(rn,3,ename,null)) emp3
    ,max(decode(rn,4,ename,null)) emp4
    ,max(decode(rn,5,ename,null)) emp5
    ,max(decode(rn,6,ename,null)) emp6
    from depts d
    group by
     d.deptno
    ,d.dname
    
    DEPTNO     DNAME     EMP1     EMP2     EMP3     EMP4     EMP5     EMP6
    10     ACCOUNTING     KING     clark     miller     willi     -      -
    20     RESEARCH     SMITH     adams     ford     jones     scott     -
    30     SALES     ALLEN     WARD     blake     james     martin     turner
  • 9. Re: A function in a subquery is call too many times.
    JP_1442650 Newbie
    Currently Being Moderated
    chris,

    thanks, but I don't use the number of calls for a counter, I just had to reduce the number of calls to optimise the total query.
    And I still like to keep using my PL/SQL function, as it's a bit complicated to rewrite it to SQL only.
    Anyway, my problem is solved using the NO_MERGE hint or the scalar subquery.

    JP

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points