Avoiding MERGE JOIN ??

From: Agi <agichen_at_my-deja.com>
Date: Wed, 29 Sep 1999 03:03:23 GMT
Message-ID: <7srvhl$8g1$1@nnrp1.deja.com>

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 - 22:03:23 CDT