Re: ROWNUM for Page selection

From: Walt <walt_askier_at_YourShoesyahoo.com>
Date: Fri, 07 Jan 2005 11:16:45 -0500
Message-ID: <OByDd.30$UN1.5@news.itd.umich.edu>

Noel wrote:
> Dnia 1/7/2005 12:44 PM, Użytkownik Daniel Paetzold napisał:
>

>> is there any better solution for the following query
>>
>> select a from (select a, ROWNUM "ROW_SUB" from t where ROWNUM<=200) 
>> where "ROW_SUB">100;
>> to select only rows 101 to 200 from the tabledata?

>
> Yes, add primary key to table, then fill it from sequence then choose
> rows from 101 to 200 using this command.
>
> There is no "rows 101 to 200" in whole Oracle, and ROWNUM is temporary
> PSEUDO-column.

Right. There is no concept of default order in Oracle. The rows are stored as an unordered set. The only ordering is what's implied from the data itself (i.e. if you have a DATE column, you can order by that column)

If you say "SELECT * FROM mytable" there is no guarantee what order you'll get the rows back. And "SELECT * FROM mytable WHERE rownum < 100" may return different results each time you run it. Now, in practice the first of these queries will *usually* return the rows in the order in which they were inserted, and the second query will *usually* return the first 99 rows that were inserted. But this is not guaranteed, and you should never ever depend on it.

If you define a create timestamp or use a sequence for a primary key, you can depend on that ordering. That's your solution, anything else will break. (including using rowid instead of rownum)

-- 
//-Walt
//
//

Received on Fri Jan 07 2005 - 10:16:45 CST