?: Forcing optimizer to use nested loops

Hello,

I have a large table (STAGING), loaded from a file, with 1.5 million rows. I want to insert/update all rows of this table into another table (TARGET). Before I do this, I have to look up reference values from a couple small tables (REF1, REF2). Then, I have to perform validations on each row of the STAGING table before the insert/update. I am using Oracle 7.3, cost-based optimizer.

I have the following query to drive a cursor:

   select
     S.*, R1.val1, R2.val2
   from REF1 R1, REF2 R2, STAGING S

   where S.r1_id = R1.r1_id (+)
     and S.r2_id = R2.r2_id (+)
     and S.updated = 'N'  (all rows have this value)

There is an index on REF1.r1_id and one on REF2.r2_id. None on STAGING. REF1 and REF2 are analyzed and STAGING is not (but I get the same results if I do analyze STAGING.)

If I use no hints, the optimizer tries to do a merge sort. Any other hint I use (including USE_NL) does a hash join between S and REF1, then one between the result and REF2. This seems to cause a lot of activity to the temporary tablespace.

My question is: is there a way to force the optimizer to use nested loops? It seems to me the hash joins are creating a lot of I/O to the temporary table, and I would be better off if the query used nested loops.

Any help would be appreciated greatly.

Cheers,
Miguel Pascoe
Miguel.Pascoe_at_fmr.com

Sent via Deja.com http://www.deja.com/
Share what you know. Learn what you don't. Received on Wed Jun 09 1999 - 21:46:25 CDT