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

John wrote:
> Thanks guys!
> I was actually searching for USER ROWID and found this thread.
> This is the what the plan looks like. Sorry it is all skewed with some
> stats missing.
> I am having trouble understanding what the optimizer is doing in step
> 10 where it accesses BILLBOAD with USER ROWID when the table has
> already been fully scanned in step 8.

No worries mate. It looks like the SQL you are interested in understanding is different from SQL posted in this thread.

Could you please post the relevant SQL and some details on your oracle environment ( oracle version and patchset level ) as well as os platform?

That will give people trying to help a better understanding.

>
> ----------------------------------------------------------------------------------------------------
> | Id | Operation | Name | Rows |
> Bytes | Cost | Pstart| Pstop |
> ----------------------------------------------------------------------------------------------------
> | 0 | SELECT STATEMENT | | 1 |
> 2 | 260K| | |
> | 1 | SORT AGGREGATE | | 1 |
> 2 | | | |
> | 2 | VIEW | | 1 |
> 2 | 260K| | |
> |* 3 | FILTER | | |
> | | | |
> | 4 | SORT GROUP BY | | 1 |
> 572 | 260K| | |
> |* 5 | HASH JOIN | | 1 |
> 572 | 260K| | |
> | 6 | NESTED LOOPS | | 1 |
> 502 | 260K| | |
> |* 7 | HASH JOIN | | 1 |
> 186 | 260K| | |
> |* 8 | TABLE ACCESS FULL | BILLBOARD | 8 |
> 976 | 260K| 2623
> |* 9 | TABLE ACCESS FULL | BUCKET | 87 | 5568 |
> 24 | | |
> |* 10 | TABLE ACCESS BY USER ROWID| BILLBOARD | 1 |
> 316 | 1 |
> | 11 | INDEX FAST FULL SCAN | PK_BUCKET | 23462 | 1603K|
> 10 | | |
>
>
> John
>
>
> hpuxrac wrote:
> > 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 Tue Nov 21 2006 - 14:59:46 CST