It is possible to get round this problem with a query of the following form:
select c1 username, c2 fullname, c3 jobtitle, c4 totalcount from (
select c1, c2, c3, c4, rownum as rowindex from (
select u.username c1,
u.surname c2,
u.jobtitle c3,
count(*) over() c4
from users u,
where <<someClause>>
order by <<sortColIndex>>;
)) where (rowindex > <<startIndex>>) and (rownum <=
<<resultPageSize>>);
So the inner select gets *all* the matching results plus the total count,
sorted by the desired column; the next select additionally gets the rownum
into rowindex; then the outer select gets just the page of results you want,
retrieving the first <<resultPageSize>> rows from <<startIndex>>. Note that
since the 'order by' is on the inner query, it doesn't suffer from the
ordering problem that Greg mentioned.
I was warned when I first employed this technique that because the inner
query is selecting *all* matching rows (like Greg's original solution) that
it would be slow, but I found that the full query performs quite well enough
for me. If you have very large result sets then you might start to see
problems, though I haven't tried.
Al.
> -----Original Message-----
> From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]]
> Sent: 22 November 2002 00:04
> To: JDJList
> Subject: [jdjlist] Re: Performance Issues (Paging large resultsets)
>
>
>
> Hi,
>
> Your revised solution is a good idea however it will not work
> with an ORDER BY clause in the SQL statement. We use Oracle
> here and originally that was what I wanted to do but I found
> through much experimentation and consulting the Oracle docs
> that Oracle will first return the rows up to and/or including
> what was specified for the ROWNUM setting and THEN it does
> the ORDER BY!
>
> So what ends up happening is you find yourself missing some
> rows that you feel should be there such as when you run the
> statement without the ROWNUM clause using the ORDER BY and vice-versa.
>
> You can confirm this behaviour by applying the ROWNUM and
> ORDER BY clauses independent of each other or in different
> combinations in the original SQL statement to see what I'm
> trying to explain here.
>
> HTH,
> Greg.
>
>
> Don Brown <[EMAIL PROTECTED]> on 11/21/2002 04:18:46 PM
>
>
> Please respond to "JDJList" <[EMAIL PROTECTED]>
>
>
> To: "JDJList" <[EMAIL PROTECTED]>
> cc:
>
>
>
> Subject: [jdjlist] Re: Performance Issues (Paging large resultsets)
>
>
>
>
> This code looks similar to what I used. However, I'll make
> one point about making it a bit quicker (at least in the
> earlier pages).
>
> The code basically returns the entire resultset, and loops
> through it, adding rows for the page in question to a vector
> while ignoring the rows before and after those of interest.
> Okay. ORACLE provides a pseudo column called ROWNUM which is
> similar to, yet different from the LIMIT of other DBs like
> PostgresQL. SQL to the effect of "SELECT <whatever> FROM
> <whatever> WHERE <whatever> AND ROWNUM BETWEEN 100 and 120"
> ****will not work****. It will always return 0 rows. However
> it will work if you put only the upper limit on it. EG:
> "SELECT <whatever> FROM <whatever> WHERE <whatever> AND
> ROWNUM <= 120" will work fine.
>
> In other words, you need to loop and skip all rows before the
> disired rows, but you don't need to return nor loop through
> skipping the ones following.
>
> Similarily, you could change the while loop from:
> -- <snip> ---
> > while (rs.next()) {
> > nNumRows++;
> >
> > if ( (nNumRows >= p_nStartRow) && (nNumRows <
> > p_nStartRow + p_nMaxRows) ) {
> > // Populate our transaction list objects
> > TransactionList transactionlist = new
> > TransactionList(rs.getString(3), rs.getString(5), rs.getInt(6),
> > rs.getString(7), rs.getString(8),
> > rs.getBigDecimal(9, 2), rs.getBigDecimal(10, 2),
> > rs.getBigDecimal(11, 2), rs.getBigDecimal(12, 4),
> > rs.getBigDecimal(13, 4), rs.getBigDecimal(14, 4),
> > rs.getString(15), rs.getString(17),
> > rs.getString(22),
> > rs.getLong(1), rs.getString(19),
> > rs.getString(23), rs.getString(24),
> > rs.getString(25));
> > vData.addElement(transactionlist);
> > }
> > }
> --- <end snip> ----
>
> To:
> _-- <snip> ---
> > while (rs.next() && (nNumRows < p_nStartRow +
> p_nMaxRows)) {
> --- cut (rest of the loop is the same) -----
>
> to do the same thing without the pseudo column.
>
> Someone else had reply about a scrollable resultset which
> might be more effiecient than this solution, but I have no
> experience with that.
>
> Hope this helps,
> Don Brown
>
>
> On November 21, 2002 11:56 am, [EMAIL PROTECTED] wrote:
> > Ok guys I'm giving you all a gift.... I had the same problem
> understanding
> > how PetStore does it and after reading much documentation on the
> > design pattern and various other people's implementation of said
> > pattern here is my interpretation of the Page-by-Page Iterator
> > pattern...
> >
> > This is actually still being used in production for a project
> > developed from three years ago so I know it works... Don't
> give me a
> > hard time
> about
> > using a Vector though... If you want to use an ArrayList instead by
> > all means feel free to change it!
> >
> > Debug is a class I wrote for outputting values while
> developing/unit
> > testing so implement your own such as Log4J or whatever. I'ved also
> removed
> > the bulk of the SQL statement as it is somewhat proprietary but I
> > think
> you
> > will get the drift...
>
> > --- <Code is in original reply from [EMAIL PROTECTED]> ---
>
> ____________________________________________________
> To change your JDJList options, please visit:
> http://www.sys-con.com/java/list.cfm
>
> Be respectful! Clean up
> your posts before replying
> ____________________________________________________
>
>
>
>
> **************************************************************
> ****************
> This email message is confidential to the intended recipient
> and may be legally privileged. It is intended solely for the
> addressee. Access to this
> email by anyone else is unauthorized. Any unauthorised use,
> dissemination
> of the information, or copying of this message is prohibited.
> If you are
> not the intended addressee, kindly notify the sender by return e-mail
> and delete this message.
> **************************************************************
> ****************
>
>
> ____________________________________________________
> To change your JDJList options, please visit:
> http://www.sys-con.com/java/list.cfm
>
> Be respectful! Clean up
> your posts before replying
> ____________________________________________________
>
____________________________________________________
To change your JDJList options, please visit:
http://www.sys-con.com/java/list.cfm
Be respectful! Clean up your posts before replying
____________________________________________________
