Re: ROWID always increase for insert-only table?

<lderani_at_uol.com.br> wrote in message
news:8evg0r$svi$1_at_nnrp1.deja.com...
> Hello all,
>
> We had a complaint today from financial dept. about some
 long-made
> reports not being orderded correctly by date as usual. By
 further
> inspection, I realized that instead being ordered by the DATE
 column
> of the table, the select was being ordered by ROWID, since the
 table
> is very large.
> The person who did it made the assumption that, since using
 ROWID
> for ordering is much faster than using a common column, and
 the table
> never gets deleted or updated (inserts only), it would make
 sense to
> use ORDER BY ROWID instead of ORDER BY DATE_COLUMN .
> When I tried to reproduce the select on SQL*PLUS, showing
 the rowid,
> it really gave me the wrong order. After that I tried this
 select:
>
> select max(ROWID) from table
>
> and it also returned the wrong row (a rowid from a row
 inserted on
> the table on 1999, when there are many records inserted on
 2000 after
> that one), showing that the greatest ROWID really was not
 assigned to
> the last inserted row in the table.
>
> So, I had to change the 'order by' clause to use the date
 column, and
> everything worked again except by the fact that the report is
 much
> slower now.
>
> As I always believed that it should be true, can someone
 explain me
> the reason of this? We were using 7.3.4 when the report was
 made, and
> today we use 8.0.5 .
>
> Thanks for the help,
>
> Luis Derani ( lderani_at_uol.com.br )
> Syst.Analist
> Universo Online - SP - Brazil
>
>
> Sent via Deja.com http://www.deja.com/
> Before you buy.
>

as far as i know, Oracle does not guarantee that rows inserted sequentially into a table will necessarily be assigned a ROWID that is greater than the ROWID of any and all rows already in the table.

as i understand it, ROWID is assigned based on the location of the row in data block, and the location of the block within the segment.

a couple of scenarios come to mind:

the rows were not inserted in a particular order.

a previously "full" data block was put back onto the freelist when space used in the block fell below the specified <pctused> value, and inserts of new rows went into those blocks.

some of the data in the table was reloaded. Received on Fri May 05 2000 - 00:00:00 CDT