Xref: alice comp.databases.oracle.server:69177 Path: alice!news-feed.fnsi.net!netnews.com!news.maxwell.syr.edu!u-2.maxwell.syr.edu!news.augsburg.net!not-for-mail From: Martin Haltmayer Newsgroups: comp.databases.oracle.server Subject: Re: Avoiding MERGE JOIN ?? Date: Sun, 10 Oct 1999 09:59:37 +0200 Lines: 186 Message-ID: <38004769.3A322630@0800-einwahl.de> References: <7srvhl$8g1$1@nnrp1.deja.com> <37f1973c.1287963@news.demon.nl> <7sv7pn$jnb$1@nnrp1.deja.com> <7t2e51$jfk$1@toto.tig.com.au> Reply-To: Martin.Haltmayer@0800-einwahl.de Mime-Version: 1.0 Content-Type: text/plain; charset=us-ascii Content-Transfer-Encoding: 7bit X-Trace: news.augsburg.net 939542317 3443 195.222.96.85 (10 Oct 1999 07:58:37 GMT) X-Complaints-To: news@augsburg.net NNTP-Posting-Date: 10 Oct 1999 07:58:37 GMT X-Mailer: Mozilla 4.61 [en] (WinNT; I) X-Accept-Language: en,de-DE 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 wrote in message > news:7sv7pn$jnb$1@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@news.demon.nl>, > > postbus@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 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.