Skip to Content
Author's profile photo Former Member

nary nested loop vs merge join

I am running a query:

select *

from prb_marker m, prb_probe pp,voc_term vt

where m._marker_key=12184

and m._probe_key=pp._probe_key

and vt._Term_key = pp._SegmentType_key

and vt._Vocab_key = 10

which is using a MERGE JOIN and the query takes forever to run.

when I removed this:

and vt._Vocab_key = 10

the optimizer is now using NARY NESTED LOOP JOIN and the query finished normally (fast).

This query example returns 117 rows of data.

Why is there such a difference between these two queries?  The “vt._Vocab_key = 10” piece seems pretty specific, so it doesn’t make sense that this would add 2 more operations and change the query plan so dramatically.  Any thoughts?

Many thanks.

Assigned Tags

      Be the first to leave a comment
      You must be Logged on to comment or reply to a post.