Skip to Content
0
Former Member
Jul 10, 2013 at 12:08 PM

n-ary nested join vs merge join

140 Views

Query with very simply queries to foreign key values and indexes.

select count(*) from voc_term t, prb_probe p, prb_source s, prb_marker pm

where t.term = 'cdna'

and t._term_key = p._segmentype_key

and p._source_key = s._source_key

and p._probe_key = pm._probe_key

the optimizer is using n-ary nested join, 5 operators and returns count 960,000/6 seconds to execute.

when I add:

and p._marker_key = 12184

the optimizer uses a merge join, 8 operators and returns count 51/1.5 minutes to execute.

If I remove:

t.term = 'cdna'

the optimizer uses n-ary nested join, 5 operations and returns 51/< 1 second.

I would expect the addition of a more specific foreign key value to improve the performance not degrade it. I have a Sybase 12.5/solaris box that is working just fine.

We are using Sybase 15.7 with ASE 15 on a linux box. This is a demo-only installation with out-of-the-box configurations set with minor increases on tempdb and procedure cache.

Any thoughts?

Thanks.

Lori