Merge Join Poor performance

From: elain he <elainhe_at_hotmail.com>
Date: Fri, 30 Jan 2004 13:50:03 -0500
Message-ID: <BAY13-F97lUI2P2lUfO00094ac9@hotmail.com>

Hi,
Can someone explain what might be the possible reasons for Oracle to use a merge join in the following query?

The query below is an extraction from a more complex query with 5 table joins and Oracle chose to do Merge Joins on four tables even though I have indexes created and analyzed on the key columns. Every table has about 3 million records and merge joins on four of the tables uses a lot of temp tablespace and performance was extremely slow.

In the example below, I removed three of the tables and only have two table joins and it still does a merge join on one of the table.

cust - 3 million records, unique index on custid tempstage - 3 million records, composite index on (cid, sid) Both tables analyzed.

Any idea what I can do to improve the performance of the query?

Thanks in advance for any assistance!

elain

select
a.profile,
b.aux
from
cust a,
tempstage b WHERE
b.cid=a.custid;

Execution Plan

---

   0      SELECT STATEMENT Optimizer=CHOOSE (Cost=83120 Card=2026108 B
          ytes=50652700)

   1    0   MERGE JOIN (Cost=83120 Card=2026108 Bytes=50652700)
   2    1     TABLE ACCESS (BY INDEX ROWID) OF 'CUST' (Cost=12121
           Card=2174646 Bytes=28270398)

   3    2       INDEX (FULL SCAN) OF 'CUSTID_UK' (UNIQUE) (Cost=11
          888 Card=2174646)

   4    1     SORT (JOIN) (Cost=61702 Card=2026108 Bytes=24313296)
   5    4       TABLE ACCESS (FULL) OF 'TEMPSTAGE' (Cost=9298 Card=2026
          108 Bytes=24313296)

_________________________________________________________________

Let the new MSN Premium Internet Software make the most of your high-speed experience. http://join.msn.com/?pgmarket=en-us&page=byoa/prem&ST=1

---

Please see the official ORACLE-L FAQ: http://www.orafaq.com

---

To unsubscribe send email to: oracle-l-request_at_freelists.org put 'unsubscribe' in the subject line.

--
Archives are at http://www.freelists.org/archives/oracle-l/
FAQ is at http://www.freelists.org/help/fom-serve/cache/1.html


-----------------------------------------------------------------

Received on Fri Jan 30 2004 - 12:50:03 CST