Okay, so I just read my own reply. I referred to MAX() calls when you had none. I confused you with a couple of the other replies I've made this week. Sorry about that.

So to more correctly address your concern about the sorting. While it is true that sorting can be a costly step, you basically have no way around this, right? If you logically need to have the result come back in sorted order, then you have to use the ORDER BY. Now, can you alleviate the pain? Sure. First, make sure you have a large enough SORT_AREA_SIZE to handle the sorting in memory. Sorting in memory is boatloads better than sorting on disk.

Next, as you alluded in your original post, can an index help? Yes. If the table is INDEX ORDERED on an index that matches the order you want and you table-scan the table or use that index, you save the sort step. Also, if you have an index that matches your ORDER BY and it is the driving index, you will also save the sort step. NEVER remove the ORDER BY. IF the optimizer does not *have* to sort it, it won't - don't worry. Mind you, if after using this index, you do a GROUP BY or something similar, then all bets are off :)

In your case, you are probably ok. The query will start with the SB table (since no restriction on ST table) and the only usable restriction is the one on the REGISTER_DATE field. So as long as your range is short (so the optimizer will actually use the index as opposed to a table scan), then you are good to go. Without the tables, I can't guarantee this, but I would guess that the optimizer is going to use your index and not have to take the time to sort the results because the index order matches the ORDER BY order.

Hope this helps,

- Chris