On 18 Mar 2004 at 14:48, Bradley Tate wrote:

>I have been trying to get the following reasonably simple SQL to
>optimize but cannot. Every time it's run it does a natural scan on
>IDRelate rather than using the index, which makes it much slower.
>
>select
> InvHead.IDRef,
> InvHead.InvHeadRef,
> InvHead.IHStatus,
> InvHead.FeePlanRef, idrelate.idlinkref
> from
> InvHead
> inner join IDRelate on
> idrelate.IDRef = InvHEad.IDRef and IDRelate.Capacity = 'ADVISOR'
> left outer join acctname on invhead.invheadref = acctname.invheadref
>where invhead.invheadref = 'IH00025809'

Try to move "IDRelate.Capacity = 'ADVISOR'" into WHERE part.
As a last resort you can put this query into selectable SP and make
joins by hands.

SY, Dimitry Sibiryakov.