Re: nested loop anti join vs hash anti join

<dbaplusplus_at_hotmail.com> wrote in message news:1143515233.373501.206930_at_j33g2000cwa.googlegroups.com...
>I am using Oracle 9.2.0.5 on HP UNIX 11i. I have a query which was
> taking 5 and half minutes. It uses not in sub query. Explain plan shows
> using NESTED LOOP ANTI JOIN. I could not undestand why it was takiing
> so nong. I added a HASH_AJ hint to not in subquery and my time reduced
> to 5 seconds (yes 5 seconds). It looks to me some kind of bug in netsed
> loop anti joins in 9.2.0.5. NL Anti joins are supposed to efficient but
> they appear to take too much time.
>

Never be too quick to say bug.

Just because it doesn't work once for you, and you can't figure out why, that doesn't mean it's a bug. First, you have to generate a case where you KNOW what should happen, with no room for ambiguity, and find that it still goes wrong. (Even then, Oracle Corp. may say it's not a bug because it's not specified to do it any other way).

In this case, the problem looks like a standard selectivity issue. The critical lines of both plans are simply the three lines for

    (anti-join, child1, child2).

> 1 0 HASH JOIN (ANTI) (Cost=542 Card=1 Bytes=78)
> 2 1 NESTED LOOPS (Cost=269 Card=1 Bytes=68)
> 8 1 VIEW OF 'VW_NSO_1' (Cost=272 Card=1 Bytes=10)

> 1 0 NESTED LOOPS (ANTI) (Cost=541 Card=1 Bytes=78)
> 2 1 NESTED LOOPS (Cost=269 Card=1 Bytes=68)
> 8 1 VIEW OF 'VW_NSO_1' (Cost=272 Card=1 Bytes=10)

Note that line 2 in both cases shows "Card=1" - so the optimizer thinks that child 2 will have to be executed once in both cases.

But the cost of performing the JOIN part of the hash join is always a little bit greater than zero. So you get:

    NL cost = 269 + 272 = 541
    HA cost = 269 + 272 + a little bit = 542.

Check the execution row stats (from a trace file), and see what the figures are for line 8 - I'd guess that the number of rows returned is more like a couple of hundred than just the one that the optimizer has predicted. Then try to figure out why the optimizer has got that bit of the prediction wrong.

-- 
Regards

Jonathan Lewis
http://www.oracle.com/technology/community/oracle_ace/ace1.html#lewis

The Co-operative Oracle Users' FAQ
http://www.jlcomp.demon.co.uk/faq/ind_faq.html

Cost Based Oracle: Fundamentals
http://www.jlcomp.demon.co.uk/cbo_book/ind_book.html