Please help tuning a query

From: Ryan Gaffuri <rgaffuri_at_cox.net>
Date: 7 May 2003 12:49:51 -0700
Message-ID: <1efdad5b.0305071149.61d60932@posting.google.com>

I have a staging table and a production table. I need to update my production table based based on what is in the staging table. So I have the following cursor.

According to Guy Harrison's book running a cursor and then updating based on the rowid is supposed to be faster, however, I think the CBO is writing bad plan.

I just used dbms_stats with estimate=20 and Cascade=>true on each of the tables.

select b.col1,
       b.col2,
       b.etc

from stage_Schema.tab s, prod_schema.tab p where s.col2 = p.col2
  and s.col3 = p.col3
  and so on...

All of the primary key columns are joined. So I think the CBO should do a nested loop join with a fast full index scan. Each table has exactly the same number of rows.

Here is my explain plan. Why is it using a sort merge? The update with the where exists is actually alot faster.

Operation	              Object Name	Rows	Bytes	Cost	
SELECT STATEMENT Hint=CHOOSE		        2 M	 	102385  	 	  MERGE JOIN   
 		2 M	398 M	102385  	  INDEX FULL SCAN	PK_IND_TAB	2 M	97 M	23494  	 	
     	       SORT JOIN		2 M	298 M	74520  	 	      	     TABLE ACCESS
FULL	TAB	2 M	298 M	4371

Sorry about the formatting. All the columns in the join are part of the primary key. I can force an index scan on the inner table, but I cant force a nested loop join??? Received on Wed May 07 2003 - 14:49:51 CDT