Also look at the oracle analytical functions, in particualr "rank"
--- [EMAIL PROTECTED] wrote:
>
> Yeah rownum is Oracle specific.... Thanks for the tip! I'll give it a try.
>
> Greg.
>
>
>
>
>
> [EMAIL PROTECTED] on 2002/11/22 08:33:28 AM
>
> Please respond to "JDJList" <[EMAIL PROTECTED]>
>
> To: "JDJList" <[EMAIL PROTECTED]>
> cc:
>
> Subject: [jdjlist] Re: Performance Issues (Paging large resultsets)
>
>
> The main difference is that my solution doesn't return the whole result set
> from the DB, only the page you're interested in, which might be an issue
> if,
> say, you're query selects 100,000 rows in total but you only want to see
> pages of 50. But the downside is that I think it'll only work on Oracle
> ('rownum' is Oracle-specific, I think!).
>
> Regards,
> Al.
>
>
> > -----Original Message-----
> > From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]]
> > Sent: 22 November 2002 13:26
> > To: JDJList
> > Subject: [jdjlist] Re: Performance Issues (Paging large resultsets)
> >
> >
> >
> > I have also found that my original solution performs well
> > regardless of how big the result set is... Granted if it
> > returns say 5000 rows then you might see an initial hit to
> > bring all of it back however on subsequent requests to the
> > database it is fast as the original result set will be cached
> > by the RDMS. This works for both Oracle and DB2 as those are
> > the two main systems we use here. I would imagine the
> > behaviour would be the same for any other major RDMS such as
> > M$ SQLServer, etc.
> >
> > Please keep in mind that my solution has been in production
> > for the last three years with no issues reported so it does
> > work well...
> >
> > Greg.
> >
> >
> >
> >
> >
> > [EMAIL PROTECTED] on 2002/11/22 04:53:35 AM
> >
> > Please respond to "JDJList" <[EMAIL PROTECTED]>
> >
> > To: "JDJList" <[EMAIL PROTECTED]>
> > cc:
> >
> > Subject: [jdjlist] Re: Performance Issues (Paging large resultsets)
> >
> >
> > 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.
>
>
>
>
>
> ******************************************************************************
> 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
> ____________________________________________________
=====
Mark Zawadzki Performance Engineer/DBA/Programmer extraordinaire� [EMAIL PROTECTED]
[EMAIL PROTECTED]
"Democracies die behind closed doors," - Judge Damon Keith
"The people of this country, not special interest big money, should be
the source of all political power. Government must remain the domain of
the general citizenry, not a narrow elite." - Sen. Paul Wellstone
__________________________________________________
Do you Yahoo!?
Yahoo! Mail Plus � Powerful. Affordable. Sign up now.
http://mailplus.yahoo.com
____________________________________________________
To change your JDJList options, please visit:
http://www.sys-con.com/java/list.cfm
Be respectful! Clean up your posts before replying
____________________________________________________
