Re: Avoiding MERGE JOIN ??

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.
Received on Tue Sep 28 1999 - 23:40:52 CDT