Originally posted by padders
Because of the logic of anti-join you must exclude the possibility of NULLs on either side of the join.

For columns or expressions not obviously NULL to the optimizer this is typically done by adding IS NOT NULL conditions to all columns or expressions referenced on BOTH sides of the in the NOT IN.
Its not the Q of "not null" at all, for the hash aj to be an option..

PS below.. the same query uses hash_aj if at all its run as query.. but when made as subqry in the update/merge statement it uses filter..

( here ofcourse i used not exists insead of not in, which should not make much difference ).. I tried with this not exists also in the merge statement still it goes for filter there....

Code:
PRO> explain plan set statement_id = 'ABHAY' for
  2  select * from shipment_line sd
  3  where
  4    not exists (select /*+ hash_aj */ null from temp_slordt s where
  5                      S.SALES_ORGANIZATION_CD       =    Sd.SALES_ORGANIZATION_CD  And
  6                      S.SALES_ORDER_NO              =    Sd.REFERENCE_DOCUMENT_NO And
  7                      Sd.REFRENCE_DOCUMENT_ITEM_NO  =    S.sales_order_item_no And
  8                      SUBSTR(s.SOURCE_NM,-3,3)      =    SUBSTR(sd.SOURCE_NM,-3,3)) and
  9    sales_document_type <> '??' and --rownum > 0 and
 10    exists (select null from temp_slsord s where
 11                      S.SALES_ORGANIZATION_CD      =    Sd.SALES_ORGANIZATION_CD  And
 12                      S.SALES_ORDER_NO             =    Sd.REFERENCE_DOCUMENT_NO And
 13                      SUBSTR(s.SOURCE_NM,-3,3)     =    SUBSTR(sd.SOURCE_NM,-3,3));

Explained.

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

  Id  Par  Pos  Ins Plan
---- ---- ---- ---- ----------------------------------------------------------------------------------------------------------------------------------------------------------------
   0      ####        SELECT STATEMENT (choose)     Cost (4743,6367,1139693)
   1    0    1          HASH JOIN    (anti) PARALLEL_TO_SERIAL Cost (4743,6367,1139693)
   2    1    1            HASH JOIN    (semi) PARALLEL_TO_PARALLEL Cost (3848,6431,964650)
   3    2    1              PARTITION RANGE    (all) PARALLEL_COMBINED_WITH_PARENT Pt id: 3 Pt Range: 1 - 4
   4    3    1    1           TABLE ACCESS (analyzed)  WWCIW_DBA SHIPMENT_LINE (full) PARALLEL_COMBINED_WITH_PARENT Pt id: 3 Pt Range: 1 - 4 Cost (3501,643102,81030852)
   5    2    2    3         TABLE ACCESS (analyzed)  WWCIW_SAP TEMP_SLSORD (full) PARALLEL_FROM_SERIAL Cost (165,79119,1898856)
   6    1    2    2       TABLE ACCESS (analyzed)  WWCIW_SAP TEMP_SLORDT (full) PARALLEL_FROM_SERIAL Cost (891,310268,8997772)
PS : In the table temp_slordt none of the columns are with "NOT NULL"..

Abhay.