Merge Join Poor performance

  • From: "elain he" <elainhe@xxxxxxxxxxx>
  • To: oracle-l@xxxxxxxxxxxxx
  • Date: Fri, 30 Jan 2004 13:50:03 -0500

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!


cust a,
tempstage b WHERE

Execution Plan
   0      SELECT STATEMENT Optimizer=CHOOSE (Cost=83120 Card=2026108 B

   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 

Please see the official ORACLE-L FAQ:
To unsubscribe send email to:  oracle-l-request@xxxxxxxxxxxxx
put 'unsubscribe' in the subject line.
Archives are at
FAQ is at

Other related posts: