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

John wrote:
> Hi,
>
> Can anyone tell me what
> TABLE ACCESS (BY USER ROWID)
> means? Thanks!

Now that Jerome noted where I could see the original SQL statement ( dooh ) you see this SQL in the original query ...

AND addr.ROWID(+) = F_Get_Address_Rowid

Where apparently ROWIDs are being retrieved by some PLSQL function?

Anyway that part of the explain plan shows how the execution plan is accessing the addr table by the ROWID.

>
> 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'
>
>
> John
>
>
> 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?
> >
> > Tomas
Received on Mon Nov 20 2006 - 18:27:56 CST