Re: Avoiding MERGE JOIN ??

One "side effect" of calling PL/SQL functions from a SQL statement which contains PL/SQL functions is that the SQL statements executed in the Function are not executed in the same read-consistent transaction as the main SQL statement. i.e. any changes to the database after the main SQL begun to execute are visable to the functions SQL each time it is called (and it in turn may see a different database state from execution to execution.

Dows anyone know if the same happens if before executing the main SQL statement, you start a read only transaction?

Dave Waterworth

Power Systems Consultants NZ Ltd

Agi <agichen_at_my-deja.com> wrote in message news:7sv7pn$jnb$1_at_nnrp1.deja.com...
> Hi,Sybrand
> Thanks for your help!
> Using PL/SQL function seems fast than table join.
> As you say,PL/SQL have side effects ?? (but it's faster in my case).
> Besides, how to get better performance in my case without PL/SQL
> function ??
>
> Any idea ?
>
> Thanks !!
>
> Agi
>
> In article <37f1973c.1287963_at_news.demon.nl>,
> postbus_at_sybrandb.demon.nl (Sybrand Bakker) wrote:
> > Hi Agi,
> > No, the first method isn't necessarily better. There are calls to that
> > function in your statement, and you don't see them included in the
> > explain plan results, except in your 27 recursive calls.
>
>
> > In the second statement you are forcing an index to be used. This
> > doesn't tell us what the optimizer would have done without hints.
> > Also, your plan uses an index pk_bcif, which from it's name looks like
> > the primary key, but then this index is not unique.
> > Looks like we need to have the table definition including primary keys
> > and other indexes to provide a sound judgment.
> > Usually, selects in pl/sql functions called in other selects is to
> > avoid at all cost.
> >
> > Hth,
> >
> > Sybrand Bakker, Oracle DBA
> >
> > On Wed, 29 Sep 1999 03:03:23 GMT, Agi <agichen_at_my-deja.com> wrote:
> >
> > >Hi,theres,
> > > I have to tables tmp_tavg(500,000 rows),tbl_bcif (600,000 rows).
> > >
> > > What's the most difference between the follow two methods?
> > > "dis" is a package, and "cifname" is a function to return name
> > > in tbl_bcif.
> > >
> > > method1 seems better than method2.
> > > Does method1 really better than method2 ??
> > > Any side effect ??
> > >
> > > Any idea ?
> > >
> > >
> > > Many thanks !!
> > >
> > >Agi
> > >
> > >
> > >
> > >method1
> > >
> > > 1 select a.unino,dis.cifname(a.unino) name
> > > 2 from tmp_tavg a
> > > 3 where a.unino like 'A%'
> > > 4* and rownum <10
> > >SQL> /
> > >...
> > >
> > >
> > >
> > >Elaps d: 00:00:01.26
> > >
> > >Execution Plan
> > >----------------------------------------------------------
> > > 0 SELECT STATEMENT Optimizer=CHOOSE (Cost=2 Card=23877
> Bytes=5
> > > 01417)
> > >
> > > 1 0 COUNT (STOPKEY)
> > > 2 1 INDEX (RANGE SCAN) OF 'IDX_TMP_ID2' (NON-UNIQUE)
> (Cost=2
> > > Card=23877 Bytes=501417)
> > >
> > >Statistics
> > >----------------------------------------------------------
> > > 27 recursive calls
> > > 0 db block gets
> > > 49 consistent gets
> > > 0 physical reads
> > > 0 redo size
> > > 859 bytes sent via SQL*Net to client
> > > 729 bytes received via SQL*Net from client
> > > 4 SQL*Net roundtrips to/from client
> > > 1 sorts (memory)
> > > 0 sorts (disk)
> > > 9 rows processed
> > >
> > >
> > >method2
> > >
> > >SQL> select /*+ index (tbl_bcif pk_bcif)*/ a.unino,b.name
> > > 2 from tmp_tavg a, tbl_bcif b
> > > 3 where a.unino=b.unino
> > > 4 and a.unino like 'A%'
> > > 5 and rownum <10;
> > >SQL>/
> > >......
> > >
> > >
> > >Elapsed: 00:00:05.10
> > >
> > >Execution Plan
> > >----------------------------------------------------------
> > > 0 SELECT STATEMENT Optimizer=CHOOSE (Cost=706 Card=23877
> Bytes
> > > =1217727)
> > >
> > > 1 0 COUNT (STOPKEY)
> > > 2 1 MERGE JOIN (Cost=706 Card=23877 Bytes=1217727)
> > > 3 2 TABLE ACCESS (BY INDEX ROWID) OF 'TBL_BCIF' (Cost=652
> > > Card=28897 Bytes=866910)
> > >
> > > 4 3 INDEX (RANGE SCAN) OF 'PK_BCIF' (NON-UNIQUE)
> (Cost=1
> > > 4 Card=28897)
> > >
> > > 5 2 SORT (JOIN) (Cost=54 Card=23877 Bytes=501417)
> > > 6 5 INDEX (RANGE SCAN) OF 'IDX_TMP_ID2' (NON-UNIQUE)
> (Co
> > > st=2 Card=23877 Bytes=501417)
> > >
> > >
> > >Statistics
> > >----------------------------------------------------------
> > > 10 recursive calls
> > > 5 db block gets
> > > 180 consistent gets
> > > 3 physical reads
> > > 0 redo size
> > > 852 bytes sent via SQL*Net to client
> > > 776 bytes received via SQL*Net from client
> > > 4 SQL*Net roundtrips to/from client
> > > 1 sorts (memory)
> > > 1 sorts (disk)
> > > 9 rows processed
> > >
> > >
> > >
> > >
> > >
> > >
> > >Sent via Deja.com http://www.deja.com/
> > >Before you buy.
> >
> >
>
>
> Sent via Deja.com http://www.deja.com/
> Before you buy.
Received on Fri Oct 01 1999 - 08:46:36 CDT