Can I count on the ordering of HASH JOIN OUTER ?

I'm trying to optimize an SQL query which involves an outer join. But I need a guarantee about the ordering: I need to know that all matches for the driving table (the one that's always present) will be consecutive. I don't necessarily need the driving table records to appear in a particular order as long as they aren't intermingled.

I really don't want to tack on an ORDER BY clause because that would force a totally superfluous sort on a rather large result set on a column different from the index range scan that's feeding the join.

I imagine a nested loop would guarantee this but it seems to be much slower, presumably because the best index is only on one of the join columns so it has to grovel through lots of extra records for each nested loop iteration.

--
greg Received on Sun Nov 07 1999 - 01:11:19 CST