Calling function from select statement is slower then using subselect in select

Home -> Community -> Usenet -> c.d.o.misc -> Calling function from select statement is slower then using subselect in select

From: Tomeo <tkepic_at_gmail.com>
Date: 25 Oct 2006 05:54:13 -0700
Message-ID: <1161780853.040959.72380@i3g2000cwc.googlegroups.com>

Hi, I have SQL query, that I'm trying to tune up.

If I simplify it, I can say: When I run my select statement, where I'm calling function
(that is just returning ABS(SUM) value) in main query, then it takes much more longer, than if I replace function with subselect(subquery). Why this happens? I'm expecting almost the same execution time. Please see query, execution time, explain plan and statistic below.

SQL*Plus: Release 10.1.0.4.2
Connected to:
Oracle9i Enterprise Edition Release 9.2.0.7.0 - Production With the Partitioning, Oracle Label Security, OLAP and Oracle Data Mining options
JServer Release 9.2.0.7.0 - Production

set timing on
set autotrace traceonly explain statistics PROMPT ORIGINAL QUERY
SELECT

       --DISTINCT
       accd.tbraccd_term_code Term,
       p.spriden_id "VEC",
       Fz_Sbalunapplied2(accd.tbraccd_detail_code, accd.tbraccd_pidm)
"Balance",
       s.spriden_id "Person"
FROM
      tbbdetc detc,
      spriden s,
      spriden p,
      tbraccd accd,
      spraddr addr

WHERE accd.tbraccd_crossref_detail_code = detc.tbbdetc_detail_code

AND   s.spriden_pidm = accd.tbraccd_crossref_pidm
AND   p.spriden_pidm = accd.tbraccd_pidm
AND   s.spriden_change_ind IS NULL
AND   p.spriden_change_ind IS NULL

AND detc.tbbdetc_dcat_code IN ('FEE','TUI') AND p.spriden_entity_ind = 'C'
AND addr.ROWID(+) = F_Get_Address_Rowid (s.spriden_pidm,'ENRLADDR','A',SYSDATE,1,'S',NULL) AND accd.tbraccd_term_code LIKE '200400%';

3324 rows selected.

Elapsed: 00:02:27.80

Execution Plan

0 SELECT STATEMENT Optimizer=RULE 1 0 NESTED LOOPS (OUTER)

   2    1     NESTED LOOPS
   3    2       NESTED LOOPS
   4    3         NESTED LOOPS
   5    4           TABLE ACCESS (BY INDEX ROWID) OF 'TBRACCD'
   6    5             INDEX (RANGE SCAN) OF 'TBRACCD_CREDITS_INDEX' (N
        ON-UNIQUE)
   7    4           TABLE ACCESS (BY INDEX ROWID) OF 'TBBDETC'
   8    7             INDEX (UNIQUE SCAN) OF 'PK_TBBDETC' (UNIQUE)
   9    3         TABLE ACCESS (BY INDEX ROWID) OF 'SPRIDEN'
  10    9           INDEX (RANGE SCAN) OF 'SPRIDEN_KEY_INDEX' (UNIQUE)
  11    2       INDEX (RANGE SCAN) OF 'SPRIDEN_KEY_INDEX' (UNIQUE)
  12    1     TABLE ACCESS (BY USER ROWID) OF 'SPRADDR'

Statistics

        29956  recursive calls
               0  db block gets
   15369502  consistent gets
               0  physical reads
               0  redo size
        68024  bytes sent via SQL*Net to client
          2861  bytes received via SQL*Net from client
           223  SQL*Net roundtrips to/from client
          6662  sorts (memory)
               0  sorts (disk)
          3324  rows processed

set autotrace off
PROMPT SOURCE FOR function Fz_Sbalunapplied2 select text from all_source where name = UPPER('Fz_Sbalunapplied2') order by line asc;

TEXT

FUNCTION Fz_Sbalunapplied2(DETAIL_CODE IN VARCHAR2, PIDM IN NUMBER)

RETURN NUMBER
AS

     RN_UNAPPLIED NUMBER (17,2) := 0;
BEGIN
  SELECT ABS(SUM(NVL(ACCD.TBRACCD_BALANCE,0)))        INTO RN_UNAPPLIED
    FROM TBRACCD ACCD,

                TBBDETC DETC

WHERE ACCD.TBRACCD_DETAIL_CODE = DETC.TBBDETC_DETAIL_CODE

       AND  ACCD.TBRACCD_DETAIL_CODE = DETAIL_CODE
       AND  ACCD.TBRACCD_PIDM = PIDM
       AND  DETC.TBBDETC_TYPE_IND = 'P';

RETURN RN_UNAPPLIED;
END Fz_Sbalunapplied2;

16 rows selected.

Elapsed: 00:00:00.12

<b>Here I replace function with query, that is inside of function: </b> SELECT --DISTINCT

         accd.tbraccd_term_code Term,
         p.spriden_id "VEC",
         (SELECT ABS(SUM(NVL(ACCD.TBRACCD_BALANCE,0)))
             FROM TBRACCD ACCD,
                        TBBDETC DETC
           WHERE ACCD.TBRACCD_DETAIL_CODE = DETC.TBBDETC_DETAIL_CODE
                 AND ACCD.TBRACCD_DETAIL_CODE =
accd.tbraccd_detail_code
                 AND ACCD.TBRACCD_PIDM = accd.tbraccd_pidm
                 AND DETC.TBBDETC_TYPE_IND = 'P') "Balance",
         s.spriden_id "Person"
FROM
      tbbdetc detc,
      spriden s,
      spriden p,
      tbraccd accd,
      spraddr addr
WHERE accd.tbraccd_crossref_detail_code = detc.tbbdetc_detail_code
      AND   s.spriden_pidm = accd.tbraccd_crossref_pidm
      AND   p.spriden_pidm = accd.tbraccd_pidm
      AND   s.spriden_change_ind IS NULL
      AND   p.spriden_change_ind IS NULL
      AND   detc.tbbdetc_dcat_code IN ('FEE','TUI')
      AND   p.spriden_entity_ind = 'C'
      AND   addr.ROWID(+) = F_Get_Address_Rowid
(s.spriden_pidm,'ENRLADDR','A',SYSDATE,1,'S',NULL)
      AND   accd.tbraccd_term_code LIKE '200400%';

3324 rows selected.

Elapsed: 00:00:04.87

Execution Plan

0 SELECT STATEMENT Optimizer=RULE 1 0 SORT (AGGREGATE)

   2    1     NESTED LOOPS
   3    2       TABLE ACCESS (FULL) OF 'TBRACCD'
   4    2       TABLE ACCESS (BY INDEX ROWID) OF 'TBBDETC'
   5    4         INDEX (UNIQUE SCAN) OF 'PK_TBBDETC' (UNIQUE)
   6    0   NESTED LOOPS (OUTER)
   7    6     NESTED LOOPS
   8    7       NESTED LOOPS
   9    8         NESTED LOOPS
  10    9           TABLE ACCESS (BY INDEX ROWID) OF 'TBRACCD'
  11   10             INDEX (RANGE SCAN) OF 'TBRACCD_CREDITS_INDEX' (N
          ON-UNIQUE)
  12    9           TABLE ACCESS (BY INDEX ROWID) OF 'TBBDETC'
  13   12             INDEX (UNIQUE SCAN) OF 'PK_TBBDETC' (UNIQUE)
  14    8         TABLE ACCESS (BY INDEX ROWID) OF 'SPRIDEN'
  15   14           INDEX (RANGE SCAN) OF 'SPRIDEN_KEY_INDEX' (UNIQUE)
  16    7       INDEX (RANGE SCAN) OF 'SPRIDEN_KEY_INDEX' (UNIQUE)
  17    6     TABLE ACCESS (BY USER ROWID) OF 'SPRADDR'

Statistics

26632 recursive calls

0 db block gets
125433 consistent gets

         0  physical reads
         0  redo size

68029 bytes sent via SQL*Net to client 2861 bytes received via SQL*Net from client 223 SQL*Net roundtrips to/from client 6662 sorts (memory)

0 sorts (disk)
3324 rows processed

Why when I use function instead subquery, my query takes so long? What I'm doing wrong?

Tomas Received on Wed Oct 25 2006 - 07:54:13 CDT