Re: Avoiding MERGE JOIN ??

I assume that this behaviour is achieved by running the PL/SQL block in an autonomous transaction independent of the original one.

Therefore you always specify the "read only" for the original transaction, not the PL/SQL one.

Please look up Thomas Kyte's page where he explains very interesting things about the autonomous transactions.

Martin

Dave Waterworth wrote:
>
> 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 Sun Oct 10 1999 - 02:59:37 CDT