Hi Maya,
I'm puzzled by your simplified query and what you write. First and
foremost since it doesn't make sense to use a left outer join when
the right table of that join is referred to in the where clause
(except where <alias.field> is null). In what way is this query
different from

select *
from NL_Transactions NLT
join JC_Allocations JCA
on (JCA.TranNo = NLT.ID)
left outer join JC_Jobs J
on (J.JobNo = JCA.JobNo)
where (JCA.JobNo = :IJobNo)

Using left outer join between NLT and JCA, you force the optimizer
to evaluate NLT first, and since that table is not in your where
clause it has to go NATURAL. You cannot change this and there's no
way you can make it faster when you want to return everything in
that table. Having JCA in your where clause, that is evidently not
what you intend to do, but that is still basically what you've told
the optimizer with your left join clause (by the way, you should
also specify the fields rather than use *)

Sorry I cannot give you any more help with the supplied information,
Set

--- In firebird-support@yahoogroups.com, "Maya McLeod" wrote:
> Hi,
>
> Help please!
>
> I am having trouble trying to get a query to make use of indexes
> in order to make it faster.
>
> The query (simplified) is as follows:
>
>  select *
> from NL_Transactions NLT
> left outer join JC_Allocations JCA
> on (JCA.TranNo = NLT.ID)
> left outer join JC_Jobs J
> on (J.JobNo = JCA.JobNo)
> where (JCA.JobNo = :IJobNo)
>
> Plan
> PLAN JOIN (JOIN (NLT NATURAL,JCA INDEX (JC_ALLOCATIONS_IDX2)),
> J INDEX (RDB$PRIMARY88))

...

> There are 22763 records in NL_TRANSACTIONS, 7 records in
> JC_ALLOCATIONS and 1 record in JC_JOBS.
> Not sure why it would be reading JC_ALLOCATIONS 409734 times!
>
> I cannot change the query to select from JC_JOBS, and left outer
> join on the other 2 tables due to the other logic required by the
> report, which I have removed from this example in order to
> simplify it.
>
> Thanks in Advance
>
> Maya McLeod