At 10:20 PM 21/01/2004 +0000, you wrote:

>While I understand that the where statement will cut the set down, I
>guess I just do not understand why it has to go to the LOOKUP_CODES
>for every record in the set times 3 (because I am joining to it 3
>times) where there is an order by statement. It seems like it would
>be close to the same amount of hits to each table with or without a
>ORDER BY statement. What am I missing here?|

Each of those subqueries is (in effect) on a separate table - that's the
point of re-entrant queries. Inside the engine, it's managing three
separate cursors inside that table.

The way the plan is, it is visiting LOOKUP_CODES three times for each row
controlled by the ORDER BY clause (once for each subquery). Without the
ORDER BY clause, it can invert the stream that controls the searches inside
the subqueries and let the pointers in the three LOOKUP_CODES streams
control each subquery, respectively. Hence, each LOOKUP_CODES row gets
visited once per subquery.

/hb