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

I have some doubts about the wisdom of the approach represented by these MySQL select statements. It seems to me that each one is a separate query, unrelated to the previous or next query, and therefore the result set seen by the user could be corrupted by the occurrence of inserts and deletes committed in the users_table while this page-by-page browsing is ongoing. (Unless the equivalent of SET TRANSACTION READ ONLY is in effect to ensure that multiple queries have the same read-consistent data view).

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.

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

Note: the ability to include ORDER BY in a subquery was introduced in Oracle8i (not sure which exact version)

Martin Doherty

Steve wrote:

> I have seen this question pop up a lot. Because I think people are not
> asking properly, they are not getting the answers they are actually
> seeking.
>
> MySQL has a very nice LIMIT clause. You can pass 2 arguments to the
> LIMIT clause.
>
> --> LIMIT <beginning offset row>, <number of rows to return>
>
> So for example, in the following SQL statement,
>
> --> SELECT username FROM users_table ORDER BY username LIMIT 0,10
>
> the usernames are sorted and the top 10 are returned. Now if you want
> the next 10 on the sorted list, use
>
> --> SELECT username FROM users_table ORDER BY username LIMIT 10,10
>
> then the next 10 on the sorted list, use
>
> --> SELECT username FROM users_table ORDER BY username LIMIT 20,10
>
> Note this is very nice especially when you are creating a web page
> where you want to navigate through the sorted usernames (displaying
> only 10 users per page). you can hit next page and get the next 10.
> notice in this case, the only differences between the webpages, is
> just one number in the SQL statment; all the pages get 10 exact
> records and all you need to do is display them.
>
> On the 51th page, all you need to use is the following SQL statment:
>
> --> SELECT username FROM users_table ORDER BY username LIMIT 500,10
>
> *************
>
> Now on to Oracle, MSSQL, and Others, which I know very little about.
>
> In MSSQL, people tell me to use the TOP statement. But this only can
> retrieve the top of the sorted list - I dont have the ability to
> choose the offset. So if I want to display the 51th page, I would have
> to use
>
> --> SELECT TOP 500 username FROM users_table ORDER BY username
>
> Which grabs 500 records! not efficient for only wanting 10 records in
> a sorted list. Imagine if there was enough records for 10,000 pages.
> Then I would have to grab many records for only wanting 10. There must
> be a better way. How are you MSSQL people doing it?? What is the most
> efficient way possible for this case? I did see somewhere else in a
> posting, you can use
>
> --> SELECT username
> FROM (SELECT TOP 10 username
> FROM (SELECT TOP 500 username
> FROM users_table
> ORDER BY username) as a1
> ORDER BY username desc) as a2
> ORDER BY username asc
>
> But damn how efficient is this? I may be wrong (depending how
> internally MSSQL is efficient), but it looks like its better to grab
> the records from the first MSSQL SQL statement.
>
> How about Oracle? How can you do it in Oracle? I know there is rownum,
> but i read somewhere
>
> --> SELECT username FROM users_table where rownum>=500 AND
> rownum<=510 ORDER BY username
>
> will not work. So what is the best way in Oracle??
>
> I am not looking for LIMIT equivalents in other Databases, because I
> know there isnt. Every database has different SQL syntax and
> functionality. But what I am asking is what is the best possible
> efficient way for the other databases to come close to match the
> MySQL's LIMIT clause. Place yourself into an administrator's shoes and
> ask yourself which way would you use? If you know, then share it here
> please (Share database name, version, examples if possible, and so
> on). It does not have to be limited to only Oracle and MSSQL, it can
> be about any other popular database that you know. And if you know any
> other databases that do have the LIMIT clause, then share it here
> also. Hopefully this will be not only a reference to me, but to anyone
> else that seeks to know this question. This scenerio comes up a lot!
>
> Thanks for your time and patience,
> Steve