on the nested loop (28.8%) i notice there is a no join predicate warning. How do I find which join and where the missing predicate is ?

Pearljammer1 2016-01-07 13:14:57

SQLkiwi 2016-01-08 12:07:59
If your query is not written using JOIN … ON syntax, you should start by changing that.

Assuming the ON clause is not actually missing, the next thing to look for would be any reference to the table on the inner side of the loop join, shown as Object18 in the anonymized plan. (side note: you should update to the latest version of Plan Explorer, which includes better anonymization).

There should be a clear correlation (predicate) from that table to another column or expression in the query, corresponding to a correlated subquery, exists clause or something of that nature.

It is also possible that you have misspelt otherwise incorrectly specified an alias name, or perhaps not used one at all. Make sure all your objects (tables, views, functions) have aliases and you always use them.

A final possibility is that the optimizer is simplifying away the join predicate based on metadata information (constraints), logical contradictions in your query, or implied predicates. That is pretty much impossible to deduce from an anonymized plan, though.