Re: not exactly a bug report, but surprising behaviour

Stephan Szabo <sszabo@megazone23.bigpanda.com> writes:

> Select list entries are done before order by since you can order by the
> output of a select list entry.

I understood that, in fact I can't quite figure out how Oracle could possibly
do it differently. Perhaps if the sort refers only to data available
immediately it does the sort before calculating the output columns.

This would have a couple big advantages:

1) If columns were excluded from the results from limit/offset clauses then
   possibly expensive functions in the select list wouldn't have to be
   calculated. It also means if the sort is quick but the functions slow that
   the first rows would be returned quickly to the application even if the
   total time was the same.

2) The sort could be done on pointers to the tuples rather than pushing the
   data in the tuple around in the sort. Obviously the transaction integrity
   issues are tricky with this though. But it could save a lot of memory
   pressure from sorts.

On the other hand, then you get two different behaviours depending on whether
any output columns are listed in the order clause, which is a nonobvious side
effect.

> I think the query that would give you what you want in this case is
> something like the following to force the order before doing the nextvals:

I don't need a solution for my purposes. I just created the table and then did
an update. Of course that isn't guaranteed either since it depends on the
physical layout of the records and the behaviour of update, but it worked.

--
greg