9 Replies Latest reply: Dec 8, 2012 12:56 PM by JP_1442650 RSS

    A function in a subquery is call too many times.

    JP_1442650
      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
          /* 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
            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
              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
                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
                  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
                    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
                      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
                        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
                          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