Colin,

Would you give us the DDL of your tables and the query itself.

Besid each table it would be good if you could indicate the number of rows it contains, as well as the indexes it actually has.

So it will be easier for us to see where we have to look for