Subject Re: inner join with additional conditions
Author Adam
--- In, Svein Erling Tysvær
<svein.erling.tysvaer@...> wrote:
> Hi Adam!
> > Now I have told you to always do it one way, there may be a few
> > performance hacks where you may have to break this rule to get a
> > decent plan. By moving an expression between the join condition and
> > where clause in a complex query, it may assist the optimiser. But this
> > is the exception rather than the rule, and I have never seen it on
> > such a simple query.
> This makes me curious. I cannot remember to have seen such a case,
but expect this to be due to me still being on Fb 1.5.

Hi Set,

Me too (mostly).

> In what kind of situations have you observed such behaviour?

I had a query with about 10 tables joined (mixed bag of inner and left
outer) that would for some reason at one of our customers take a long
time. After replicating their environment, I noticed it was a bad plan
and went about trying to discourage the use of the indices I thought
were unhelpful.

Come to think of it, it may have even had to change an inner join to a
left join (and I put the where is not null in the where clause
to keep the right data).

I can't confirm which of these actions actually fixed the plan, and I
wish I could remember the specific query involved. If I stumble across
it, I will try a test case.