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

Tomeo wrote:
> 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?

It is almost always faster to use just SQL instead of a combination of SQL and PLSQL function calls if you can.

Tom Kyte says ( paraphrasing ) " Do it all in SQL ( or oracle's extensions to SQL ) " before doing it another way.

When you have PLSQL functions invoked on a column oracle has to switch between processing rows and then take the value for a column in a row and context switch into a PLSQL runtime environment to execute the function ... then switch back into the processing of the row.

This takes memory and cpu overhead to accomplish that.

In contrast ( somehow ) when you use pure SQL or functions which are oracle's extension to SQL then ( somehow ) oracle keeps all of this execution basically in line while processing the rows. Received on Wed Oct 25 2006 - 08:24:52 CDT