This is from the 9i performance tuning manual: When the Optimizer Uses Sort Merge Joins The optimizer can choose a sort merge join over a hash join for joining = large amounts of data if any of the following conditions are true: -The join condition between two tables is not an equi-join.=20 -OPTIMIZER_MODE is set to RULE.=20 -HASH_JOIN_ENABLED is false.=20 -Because of sorts already required by other operations, the optimizer = finds it is cheaper to use a sort merge than a hash join.=20 -The optimizer thinks that the cost of a hash join is higher, based on = the settings of HASH_AREA_SIZE and SORT_AREA_SIZE.=20 -----Original Message----- From: oracle-l-bounce@xxxxxxxxxxxxx [mailto:oracle-l-bounce@xxxxxxxxxxxxx]On Behalf Of elain he Sent: Friday, January 30, 2004 1:50 PM To: oracle-l@xxxxxxxxxxxxx Subject: Merge Join Poor performance Hi, Can someone explain what might be the possible reasons for Oracle to use = a=20 merge join in the following query? The query below is an extraction from a more complex query with 5 table=20 joins and Oracle chose to do Merge Joins on four tables even though I = have=20 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=20 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=3Da.custid; Execution Plan ---------------------------------------------------------- 0 SELECT STATEMENT Optimizer=3DCHOOSE (Cost=3D83120 = Card=3D2026108 B ytes=3D50652700) 1 0 MERGE JOIN (Cost=3D83120 Card=3D2026108 Bytes=3D50652700) 2 1 TABLE ACCESS (BY INDEX ROWID) OF 'CUST' (Cost=3D12121 Card=3D2174646 Bytes=3D28270398) 3 2 INDEX (FULL SCAN) OF 'CUSTID_UK' (UNIQUE) (Cost=3D11 888 Card=3D2174646) 4 1 SORT (JOIN) (Cost=3D61702 Card=3D2026108 Bytes=3D24313296) 5 4 TABLE ACCESS (FULL) OF 'TEMPSTAGE' (Cost=3D9298 = Card=3D2026 108 Bytes=3D24313296) _________________________________________________________________ Let the new MSN Premium Internet Software make the most of your = high-speed=20 experience. = http://join.msn.com/?pgmarket=3Den-us&page=3Dbyoa/prem&ST=3D1 ---------------------------------------------------------------- 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 //www.freelists.org/archives/oracle-l/ FAQ is at //www.freelists.org/help/fom-serve/cache/1.html ----------------------------------------------------------------- ---------------------------------------------------------------- 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 //www.freelists.org/archives/oracle-l/ FAQ is at //www.freelists.org/help/fom-serve/cache/1.html -----------------------------------------------------------------