SQL & PL/SQL

hi experts,

I have a SQL looking like this:

select * from t
where  (t.col1,t.col2, ...)
        in     (select col1,col2, ...
                  from view1
               )  
  and not exists (
      select /*+ hash_aj */ null
        from view2
       where col1=t.col1 and col2=t.col2 and ....
      )

The optimizer generates a sql plan with nested loop anti join for the "not exists" part. It's suboptimal because there's a full scan on a global temp table in view2.

I tried rewriting it to:

  and (t.col1,t.col2, ...) not in (
      select /*+ hash_aj */ col1,col2, ...
        from view2
       where col1 is not null and col2 is not null and ...
      )

But it stills uses nested loop anti. In the hints report the hash_aj hint is marked as U(Unused).

hash_aj hint is deprecated in 10g. Is there a replacement to enforce a hash anti join?

If I populate the temp table with a lot of data and run dbms_stats.gather_table_stats it does come up with a hash anti join. I look at the outline and I see some USE_HASH, SWAP_JOIN_INPUTS hints. I would like to know if there's an easier way to hint it.

Thank you!