I'm struggling to get a very slow join to run faster. The database is a
moderately sizable transaction db with 2 main tables.

Trans is the transaction header data, Transitems contains the actual items.

A query like this ...

Select * from TRANS where STORE=x and TRAN_DATE>start_date

... runs fine.

But if I do this ...

Select * from TRANS join ITEMS on ITEMS.TRAN_ID=TRANS.ID where
TRANS.STORE=x and TRANS.TRAN_DATE>start_date

... runs over a period of minutes. There is an active index on the
ITEMS.TRAN_ID, so it should be able to find any items very quickly.

The thing is, even if the store in the query does not exist, the first
statement returns an empty result set very quickly, but the second takes
a long time regardless. So what it must be doing is performing the join
prior to the evaluation of the where clause, and so ignores all the
indices. Is there a way to get the conditional evaluation done prior to
the join, so that very few records are actually looked up, but without
using a temporary table?

Cheers
Terry