Nested Loop Has Join issue (anti)
766689Jul 20 2011 — edited Jul 25 2011Have SQL like this
SELECT poc.offer_code, pob.offer_unit_quantity, pob.batch_id
FROM wcx_promo_offer_batch pob, wcx_promo_offer_codes poc, wcx_promo_advertising_items ai
WHERE NOT EXISTS (SELECT cc.offe _code
FROM wcx_promo_code_consumption cc WHERE poc.promotion_id = cc.promotion_id AND poc.offer_code = cc.offer_code)
AND pob.promotion_id = poc.promotion_id AND pob.batch_id = poc.batch_id AND ai.batch_id = pob.batch_id AND ai.item_id = 15833719 AND ai.promotion_id = poc.promotion_id AND pob.promotion_id = 5644 AND ai.active_flag = 'Y' AND rownum < 2
NO INDEXES on correlated subquery table => wcx_promo_code_consumption
So we have a NL condiution that drives into a FTS around 30-60 times.
My question is
1) How can you tell from autotrace how many times the NL is going to do a FTS ? You can't - has to only be a 10046 trace right ? So it doesn't show you how bad it can be.
2) I can fix this with a merge_aj or hash_aj hint .......... but why oracle not able to work it out ? Which stats would DRIVE this
thx