Re: Too many CONSISTENT GETS using NOT EXISTS

Hi,
Try setting the following parameter in session level

alter session set always_anti_join=hash;

or you may use a hint hash_aj

select au.user_id, min(aa.owner) as new_owner from BC_AUTH_USERS au, BC_AUTH_ACCESS aa
where au.user_id = aa.user_id
and not exists
(select /*+ hash_aj(aa2) */ '' from BC_AUTH_ACCESS aa2 where aa2.user_id = au.user_id and
aa2.owner = au.owner)
group by au.user_id;

This will convert the filter operation in your plan in hash join. The consistant gets should decrease after that.

Thanks,
Saikat
http://saikatchak.tripod.com

-- 
Posted via Mailgate.ORG Server - http://www.Mailgate.ORG