Re: ROWID always increase for insert-only table?

> 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.

Generally, if you are using full table scan, rows are returned in the order they were inserted (so you don't even need to sort) but as of 8 (or 7, correct me if I am wrong) Oracle says this is no longer guaranteed.

However, sorting by rowid in 8 would not be a safe choice even if that guarantee were in place:
[...
An extended ROWID has a four-piece format, OOOOOOFFFBBBBBBRRR: OOOOOO: The data object number identifies the database segment (AAAAao in the example). Schema objects in the same segment, such as a cluster of tables, have the same data object number. FFF: The datafile that contains the row (file AAT in the example). File numbers are unique within a database.
BBBBBB: The data block that contains the row (block AAABrX in the example). Block numbers are relative to their datafile, not tablespace. Therefore, two rows with identical block numbers could reside in two different datafiles of the same tablespace. RRR: The row in the block.
...]

    quote from A58227]

There are three factors I can think of that could lead to wrong max on rowid:
- if your table is partitioned (new to 8), it may so happen that the max segment_id is for a partition other than the one into which the last row was inserted
- if the above is not true, and if your tablespace to which this table belongs has multiple datafiles, it may so happen that the file with max id is not the same file where the last row was inserted - (very likely) if none of the above is true, table extents may be allocated in such a way that the currently inserted to extent block numbers are less than previous extent block numbers

Kirill Richine

Sent via Deja.com http://www.deja.com/
Before you buy. Received on Sun May 07 2000 - 00:00:00 CDT