Re: not exactly a bug report, but surprising behaviour

On 4 Feb 2003, Greg Stark wrote:

> Stephan Szabo <sszabo(at)megazone23(dot)bigpanda(dot)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.

Does it always do it when it's only on input columns? Or only in some
cases? It might be interesting to see. For example, the same sort of
thing with an index existing/not existing might make a different or the
plan chosen.

> 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.

That's a useful advantage. You can probably get this effect from pushing
clauses into from list subselects as a current optimization, but it'd be
nice since that's not an obvious conversion.

> 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.

This'd be an advantage in the case of a single table. Unless you can
guarantee the join keeps the ordering properties, I don't think this'd
help with anything that's doing joins or anything more complicated.