Re: Avoiding MERGE JOIN ??

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 Thu Sep 30 1999 - 03:42:33 CDT