| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Mailing Lists -> Oracle-L -> RE: Merge Join Poor performance
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_at_freelists.org
[mailto:oracle-l-bounce_at_freelists.org]On Behalf Of elain he
Sent: Friday, January 30, 2004 1:50 PM
To: oracle-l_at_freelists.org
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
FAQ is at http://www.freelists.org/help/fom-serve/cache/1.htmlput 'unsubscribe' in the subject line.
-----------------------------------------------------------------
----------------------------------------------------------------
Please see the official ORACLE-L FAQ: http://www.orafaq.com
----------------------------------------------------------------
To unsubscribe send email to: oracle-l-request_at_freelists.org
 |
 |