I think this might solve my problem - I am going to give this logic a shot.
Thanks all for your replies..
"mcstock" <mcstockspamplug_at_spamdamenquery.com> wrote in message
news:AfGdndbXwqQRDy6iRVn-hg_at_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 - 23:35:18 CST
