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

      1 Comment
      You must be Logged on to comment or reply to a post.
      Author's profile photo Mark A Parsons
      Mark A Parsons

      Without more details (eg, query plans, table/index structures, inventory of column stats, etc) it's a bit hard to know exactly what the issue is.

      Missing (or out of date) stats on the columns referenced in the query could lead the optimizer to come up with the poor query plan.

      Less than helpful (or just plain missing) indexes could also lead the optimizer to make some bad decisions.

      Older versions of ASE 15.x's optimizer, or misconfigured settings, could lead to poor optimizer decisions.

      And the list goes on and on and on ... with the net result being that it may be a bit tough (if not impossible) to get to the root cause via a limited setting as this forum (either through discussions or blogs).

      You could try running 'update index stats' on the 3 tables in question to see if that helps the optimizer come up with a better query plan.

      Alternatively you could try limiting the optimizer to only considering NL joins by using the allrows_oltp optimization goal.  This can be set at the dataserver level (via sp_configure), session level (via 'set plan optgoal') or query level (via abstract query plan).