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!