Oracle Database Discussions

Hi,

I am trying to understand the optimizers choice in a 10GR2 Windows environment.

I have a work table with 12 million rows (no indexes) and another table that I would like to put the data that is in the work table. The target table (13 million rows) is indexed on id/effective_date via a composite index. The optimizer is choosing a hash join right semi on these tables. My SGA is relatively small 1.2GB and the PGA target is 200MB so I suspect that the memory is getting exhausted since the update takes very long. My understanding of Hash joins is that they may require lots of memory and the server I have can't supply enough for this method to work efficiently. How can I help the optimizer make a better choice?

Here is the update statement:
update daily_data tgt
set ( msci_country_h, msci_country_code_h) =
( select msci_country_h, msci_country_code_h
from stg_msci_cc src
where tgt.id = src.id and tgt.effective_date = src.effective_date
)
where exists (
select 1
from stg_msci_cc src
where tgt.id = src.id and tgt.effective_date = src.effective_date
);




Operation Object Name Rows Bytes Cost Object Node In/Out PStart PStop

UPDATE STATEMENT Optimizer Mode=ALL_ROWS 1 85884
UPDATE DAILY_DATA
HASH JOIN RIGHT SEMI 1 47 85884
TABLE ACCESS FULL STG_MSCI_CC 12 M 227 M 9276
TABLE ACCESS FULL DAILY_DATA 13 M 351 M 33191
TABLE ACCESS FULL STG_MSCI_CC 1 K 23 K 9298

Message was edited by:
ennisb