Re: Sort Order in Nested query

From: Saibabu Devabhaktuni <saibabu_d_at_yahoo.com>
Date: Thu, 31 Jan 2013 22:59:02 -0800 (PST)
Message-ID: <1359701942.2111.YahooMailNeo_at_web161304.mail.bf1.yahoo.com>

Fergal,
The reason why your sequence.nextval is failing in the sql query is because nextval is only retrieved during the actual fetch time and hence order by clause is not possible as sorting has to happen after filtering all records. Also hiding sequences in sub query or using sequences in where clause is not allowed for the same reason.

I guess Oracle's thinking behind retrieving sequences during fetch time is:

1. To reduce sequence gaps as much as possible (with order by clause, you can process million rows, and only fetch few records causing million minus 10 sequence values to go wasted.
2. To reduce contention on seq$ by avoiding frequent updates (refer to above order by clause)
3. If your query runs very longtime, you would see considerably old sequence values if the sequences are retrieved during query result set generation instead of during fetch time.
4. There is no good use case to do it other way round and it makes sense.

Thanks,
 Sai
http://sai-oracle.blogspot.com

--
http://www.freelists.org/webpage/oracle-l

Received on Fri Feb 01 2013 - 07:59:02 CET