Why isn't this predicate being evaluated remotely?

This question arises when a predicate is very selective and thus could be used to filter rows and reduce network traffic. Remote predicate evaluation also affects whether a join between two tables of the same data source can be evaluated remotely.

Areas to examine include:
  • Server options. How do the settings for the server options COLLATING_SEQUENCE and VARCHAR_NO_TRAILING_BLANKS affect where the predicate is evaluated?
  • Subquery predicates. Does this predicate contain a subquery that pertains to another data source? Does this predicate contain a subquery involving an SQL operator that is not supported by this data source? Not all data sources support set operators in a predicate.
  • Predicate functions. Does this predicate contain a function that cannot be evaluated by this remote data source? Relational operators are classified as functions. Does the function have a parameter marker in its parameter? If so, it is not evaluated at a remote data source. E.g: SELECT COUNT(*) FROM NICKNAME WHERE TELNO = REPLACE(?,'-',''), in this case, REPLACE(?,'-','') will be blocked to push down.
  • Predicate bind requirements. Does this predicate, if remotely evaluated, require bind-in of some value? If so, would it violate SQL restrictions at this data source?
  • Global optimization. The optimizer decided that local processing is more cost-effective.