Merge Join Poor performance
- From: "elain he" <elainhe@xxxxxxxxxxx>
- To: oracle-l@xxxxxxxxxxxxx
- Date: Fri, 30 Jan 2004 13:50:03 -0500
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 //www.freelists.org/archives/oracle-l/
FAQ is at //www.freelists.org/help/fom-serve/cache/1.html
-----------------------------------------------------------------
Other related posts: