Merge Join Poor performance

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@xxxxxxxxxxxxx
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
-----------------------------------------------------------------

Other related posts: