Rahul Babbar wrote:
Santosh Ramachandrula wrote:Rahul,
For your questions 1 & 2 please see similar issue at http://forum.springsource.org/archive/index.php/t-61016.html
Thanks for the above link, it indeed is the same issue.
However, the discussion(in the link) also does not seem to give a proper explanation as to why the query should be slower for the last set of records...
It could be a bug in some version of Oracle 10g or.....(well, i cant see any other reason... )
It's been a few months since somebody posted into this thread but I happened to wander through this and saw that the question as to why the query runs slower for the last set of records wasn't clearly answered... actually it was answered in http://forum.springsource.org/archive/index.php/t-61016.html
but perhaps the last poster of this thread (Rahul Babbar) had missed it?
So here's my attempt at answering it with the little knowledge that I have. This article on "Ask Tom" explains it all: http://www.oracle.com/technology/oramag/oracle/06-sep/o56asktom.html
(in particular, the section on Pagination using ROWNUM but you still have to read the whole thing to make the most sense out of it).
If you look at that query closely, you'll notice that it incorporates a top-N query (get the first :MAX_ROW_TO_FETCH rows from your query) and hence benefits from the top-N query optimization I just described. Further, it returns over the network to the client only the specific rows of interest—it removes any leading rows from the result set that are not of interest.
For the first page, your :MAX_ROW_TO_FETCH will be low (say, 20). Oracle will perform a top-N query using that value ("get me the first 20 results, sorted") then removes 0 leading rows since you're on the first page. If you're on the last set of pages, your :MAX_ROW_TO_FETCH will be much larger (say, 261159). You're only interested in displaying 20 results on your page, but Oracle still has to first perform a top-N query with the :MAX_ROW_TO_FETCH value ("get me the first 261159 results, sorted"); this takes much longer. Only afterwards will it discard the leading rows so you only end up with the 20 results to show on your page.