RE: Merge Join Poor performance

  • From: "Freeman, Donald" <dofreeman@xxxxxxxxxxx>
  • To: <oracle-l@xxxxxxxxxxxxx>
  • Date: Fri, 30 Jan 2004 13:53:46 -0500

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

Other related posts: