Re: Ultimate Question: Oracle, MSSQL, Others vs MYSQL LIMIT Statement

From: Vladimir M. Zakharychev <bob_at_dpsp-yes.com>
Date: Tue, 17 Sep 2002 13:53:36 +0400
Message-ID: <am6u3p$3jf$1@babylon.agtel.net>

> The approach I would take in Oracle is to open a cursor with my complete
> query (select username from users_table order by username), then fetch
> rows from the cursor 10 at a time. The view of the data seen by the user
> would be read-consistent with the moment in time that the cursor was
> opened (that is, the effect of any inserts or deletes by other sessions
> would not be visible to the user browsing the list). My program logic
> might include a message to the user on each page to reveal the defining
> moment ("Information current as at 9/16/02 17:01 pm") and a timer to close
> the cursor if no browsing activity detected within the threshold period.
> Since Oracle does not block readers or writers, holding the query open is
> not preventing access to the users_table for concurrent reading and
> writing.

This approach won't work for stateless web applications. Oracle session is disconnected or reused by other requests as soon as current request is serviced, it does not maintain state during the logical session established between client and HTTP server. This is just one of the many challenges of web applications database developers face. Another one is that there is no easy way for detecting logical session end - client simply closes browser window and that's it - server never knows if another request will come from that client or not. Thus maintaining a pool of dedicated connections to the database to maintain stateful logical sessions is not reasonable - they are never closed gracefully and you will end up with a multitude of dead connections eating up your valuable server resources. For a site with 10 visitors per hour this is not a big problem, but what if you get thousands of hits per hour? Stateless sessions deal with this problem, but they introduce their own - you can't use database transaction and concurrency controls in such applications. You have to deal with lost updates, you have to deal with incomplete queries (like the one in question), and with a lot of other things you normally do not see in classic client-server apps where database session is permanent.

> Regarding ROWNUM, the numeric values of this pseudo-column are assigned to
> each result row before sorting, but you can work around this by using an
> inline view, viz:
>
> select *
> from (select username from users_table order by username) u
> where u.rownum between 501 and 510

Actually, the most effective way to do ranged queries I found to date is

select *
from ( select q.*, rownum rnum

           from ( YOUR_QUERY_GOES_HERE ) q
          where rownum <= :MAX_ROW )

where rnum >= :MIN_ROW;

since it does not require to rewrite the inner query, just wrap two views around it.

-- 
Vladimir Zakharychev (bob@dpsp-yes.com)                http://www.dpsp-yes.com
Dynamic PSP(tm) - the first true RAD toolkit for Oracle-based internet applications.
All opinions are mine and do not necessarily go in line with those of my employer.

Received on Tue Sep 17 2002 - 04:53:36 CDT