Guys...

I tried all sorts of hints but still oracle goes for filter and FTS on a temp table.. I dont want to create an index for this reason..

Is there any way to force Oracle to use Hash Aj in this case..

Below is the code and plan..

Code:
PRO> ed
Wrote file afiedt.buf

  1  explain plan set statement_id = 'ABHAY' for
  2  MERGE /*+ Ordered */ INTO  Shipment_Line X
  3  USING
  4  (
  5  select /*+ Ordered */ * from shipment_line sd
  6  where
  7    (
  8      Sd.SALES_ORGANIZATION_CD       ,
  9      Sd.REFERENCE_DOCUMENT_NO       ,
 10      Sd.REFRENCE_DOCUMENT_ITEM_NO   ,
 11      SUBSTR(sd.SOURCE_NM,-3,3)
 12    )
 13    not in (select /*+ Hash_Aj */
 14                      S.SALES_ORGANIZATION_CD       ,
 15                      S.SALES_ORDER_NO              ,
 16                      S.sales_order_item_no         ,
 17                      SUBSTR(s.SOURCE_NM,-3,3)
 18            from temp_slordt s) and
 19    sales_document_type <> '??' and rownum > 0 and
 20    exists (select null from temp_slsord s where
 21                      S.SALES_ORGANIZATION_CD      =    Sd.SALES_ORGANIZATION_CD  And
 22                      S.SALES_ORDER_NO             =    Sd.REFERENCE_DOCUMENT_NO And
 23                      SUBSTR(s.SOURCE_NM,-3,3)     =    SUBSTR(sd.SOURCE_NM,-3,3))
 24  ) Y
 25  ON
 26  (
 27    X.SALES_ORGANIZATION_CD   =    Y.SALES_ORGANIZATION_CD      AND
 28    X.SHIPMENT_NO             =    Y.SHIPMENT_NO                AND
 29    X.SHIPMENT_ITEM_NO        =    Y.SHIPMENT_ITEM_NO           AND
 30    X.SOURCE_NM               =    Y.SOURCE_NM
 31  )
 32  WHEN MATCHED THEN
 33  UPDATE  SET
 34    x.sales_document_type = '??', x.reportable_flag = '?',
 35    x.sab101_flag = '?', x.country_cd = '?', x.Ship_To_Customer = '?',
 36    x.So_Entry_Date = '31-Dec-2999', x.SALES_ORDER_ITEM_SHORT_TX = '??'
 37  WHEN NOT MATCHED THEN
 38  INSERT
 39   (
 40    X.SALES_ORGANIZATION_CD          ,
 41    X.SHIPMENT_NO                    ,
 42    X.SHIPMENT_ITEM_NO               ,
 43    X.SOURCE_NM
 44   )
 45  VALUES
 46   (
 47    Y.SALES_ORGANIZATION_CD          ,
 48    Y.SHIPMENT_NO                    ,
 49    Y.SHIPMENT_ITEM_NO               ,
 50    Y.SOURCE_NM
 51*  )
PRO> /

Explained.

Elapsed: 00:00:01.39
PRO> @display_plan ABHAY

  Id  Par  Pos  Ins Plan
---- ---- ---- ---- ----------------------------------------------------------------------------------------------------------------------------------------------------------------
   0      ####        MERGE STATEMENT (choose)     Cost (4938,1,164)
   1    0    1          MERGE  WWCIW_DBA SHIPMENT_LINE
   2    1    1   12       VIEW  WWCIW_DBA
   3    2    1              NESTED LOOPS    (outer)  Cost (4938,1,303)
   4    3    1    7           VIEW  WWCIW_DBA   Cost (4937,1,177)
   5    4    1                  COUNT
   6    5    1                    FILTER
   7    6    1                      HASH JOIN    PARALLEL_TO_SERIAL Cost (4046,1,150)
   8    7    1                        SORT    (unique) PARALLEL_FROM_SERIAL
   9    8    1   10                     TABLE ACCESS (analyzed)  WWCIW_SAP TEMP_SLSORD (full)  Cost (165,79119,1898856)
  10    7    2                        PARTITION RANGE    (all) PARALLEL_COMBINED_WITH_PARENT Pt id: 10 Pt Range: 1 - 4
  11   10    1    8                     TABLE ACCESS (analyzed)  WWCIW_DBA SHIPMENT_LINE (full) PARALLEL_TO_PARALLEL Pt id: 10 Pt Range: 1 - 4 Cost (3501,32155,4051530)
  12    6    2    9                 TABLE ACCESS (analyzed)  WWCIW_SAP TEMP_SLORDT (full)  Cost (891,2,58)
  13    3    2    2           TABLE ACCESS (analyzed)  WWCIW_DBA SHIPMENT_LINE (by global index rowid)  Pt id: 13 Pt Range: ROW LOCATION - ROW LOCATION Cost (3,1,126)
  14   13    1                  INDEX (analyzed) UNIQUE WWCIW_DBA UK_SHIPMENT_LINE (unique scan) (Columns 4  Cost (2,1,)
Thx
Abhay.