Hi Svein

Will look into your comments. Unfortunately have been pulled off this
to look at another issue, it may be some time before I can get back to
this. Thanks to you and everyone else for the comments and suggestions,
very much appreciated :)

Rob Martin
Software Engineer

phone +64 03 377 0495
fax +64 03 377 0496
web www.chreos.com

Wild Software Ltd



Svein Erling Tysvær wrote:

>Hi Rob!
>
>There are at least two plans the optimizer may decide to use with
>
>SELECT *
>FROM SupplyLine sl
>JOIN SupplyOrder so ON so.SupOrdrRef = sl.SupOrdrRef
>WHERE so.EntityRef = 4
>
>(I'll write field names rather than index names this time)
>
>This is good:
>PLAN JOIN (SupplyOrder(EntityRef), SupplyLine(SupOrdrRef))
>whereas this is potentially bad:
>PLAN JOIN (SupplyLine(NATURAL), SupplyOrder(SupOrdrRef, EntityRef))
>
>(not only due to NATURAL, also because of excessive use of indexes for
>SupplyOrder)
>
>Now, the query you ended up with is your work, not mine. What I
>suggested was the opposite of what you did! I assumed it was
>SupplyLine.EntityRef and not SupplyOrder.EntityRef. I'm pretty certain
>this would have been quicker than your solution if that had been the
>whole picture, but in your final solution you introduced a few more
>indexes (for checking NULL) that requires quite a bit of knowledge of
>your data to tell whether they are useful or not.
>
>I added the +0 in order to give the optimizer a hint as to which table
>to put first in the plan. The plans used with or without the +0 may be
>very different, so don't blame it exclusively on 'low selectivity' for
>SupOrdrRef - even though low selectivity is one of the things to try
>to avoid.
>
>For the fun of it, does any of these two execute even faster?
>
>SELECT *
>FROM SupplyLine sl
>JOIN SupplyOrder so ON so.SupOrdrRef = sl.SupOrdrRef + 0
>WHERE so.EntityRef + 0 = 4 //avoid the EntityRef index
>AND sl.SupTranRef IS NULL
>AND sl.PackSlpRef IS NULL
>AND sl.CancelFlag <> 'T'
>
>and
>
>SELECT *
>FROM SupplyLine sl
>JOIN SupplyOrder so ON so.SupOrdrRef + 0 = sl.SupOrdrRef
>WHERE so.EntityRef = 4
>AND (2=0 //avoid all these indexes and change order of tables
> OR (sl.SupTranRef IS NULL
>AND sl.PackSlpRef IS NULL
>AND sl.CancelFlag <> 'T'))
>
>Set
>
>
>
>