Re: Question and suggestion regarding rownum

From: mcstock <mcstockspamplug_at_spamdamenquery.com>
Date: Thu, 13 Nov 2003 09:16:45 -0500
Message-ID: <AfGdndbXwqQRDy6iRVn-hg@comcast.com>

tom's example is good, but for this to really work, it needs an ORDER BY clause, and hence needs to be double nested, ie:

SELECT ee.*
FROM (SELECT ROWNUM AS the_rownum

              ,e.*
          FROM (SELECT   *
                    FROM emp
                ORDER BY ename) e
         WHERE ROWNUM <= 15) ee

WHERE the_rownum > 10
/

this preserves the row order
1) the order by is in the inner most query (without which the row order is not guaranteed repeatable)
2) the rownum is assigned in the first enclosing query 3) the max rownum is set in the first enclosing query, based on the ROWNUM pseudo-column
4) the min rownum is set in the 2nd enclosing query, based on the aliased ROWNUM value actually returned from the 1st enclosing query

HOWEVER.... depending on usage patterns there could be a pretty significant performance implications of this approach -vs- 1st fetching all rownums (or PKs) then using these to page thru the data:

testing on a 14,336 row emp table indexed on deptno with 3,072 records for deptno = 10:

1) 87 gets to select all rowids or empnos for deptno = 10
2) 10 gets (no surprise) to select all data for 10 rows, by rowid
3) 10 gets for each subsequent page

-vs-

1) 87 gets to select all rowids or empnos for deptno = 10
2) 31 gets (no surprise) to select all data for 10 rows, by primary key
3) 31 gets for each subsequent page

-vs-

1) 87 gets to select the 'first' page
2) 87 gets to select the 'last' page
3) 87 gets to select any page

comparing gets per page for the 3 techniques

     ROWID PK ROWNUM
      1st page 87 87 87
      Next page 10 31 87
      Per Page Averages
      1 87.00 87.00 87.00
      2 48.50 59.00 87.00
      3 35.67 49.67 87.00
      4 29.25 45.00 87.00
      5 25.40 42.20 87.00
      6 22.83 40.33 87.00

-- 
Mark C. Stock
email mcstock -> enquery(dot)com
www.enquery.com
(888) 512-2048

"Holger Baer" <holger.baer_at_science-computing.de> wrote in message
news:3FB373C8.9060803_at_science-computing.de...


> ketansweta_at_sbcglobal.net wrote:

> > Hi! Folks

> >

> > I am sure many of you have come across this problem which I am facing

and


> > hope to have some suggestions.

> > We have an web application with tones of where clauses and group by but

> > eventually the result set could be any where

> > from 1 to 10000. Now the web page would display only 1 to 10 items after

> > which if you would click on next it would show

> > from 11 to 20 - till here it is some what do able - the problem lies

when


> > the user click on directly 45th page which should show records

> > from 2000 onwards. I was thinking of using the rownum function ..

remember


> > this is a select from a 500K table - .

> >

> > Any folks who have a better idea or suggestion would be great ful.

> >

> > Thanks

> > Ketan

> >

> >

>


> Check this out (might wrap):

>
>
http://asktom.oracle.com/pls/ask/f?p=4950:8:1915869985079788436::NO::F4950_P8_DISPLAYID,F4950_P8_CRITERIA:127412348064,
>
>


> HTH,

> Holger

>

Received on Thu Nov 13 2003 - 08:16:45 CST