Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

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

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

From: John <jbradshaw777_at_yahoo.com>
Date: 21 Nov 2006 11:13:10 -0800
Message-ID: <1164136390.754908.211780@k70g2000cwa.googlegroups.com> 





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.




| 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 - 13:13:10 CST












Original text of this message













  HOME |
  ASK QUESTION |
  ADD INFO |
  SEARCH |
  E-MAIL US