Re: Comparing two rowids

> To me comparing two rowids does not make sense, I can't see how one rowid
> be greater than another?

It makes sense in (non-unique) indexes - entries in indexes are ordered by (key, rowid), so that a range scan for key = something (or key between, etc), that visits the index entries in order, will access rows located in the same block by making a single consistent get on that block.

To support the above optimization, the definition of "rowid order" has to order by (RELATIVE_FNO, BLOCK_NUMBER), or (OBJECT_NUMBER, RELATIVE_FNO, BLOCK_NUMBER) for non-restricted rowids; ordering by ROW_NUMBER is not strictly necessary, but maybe it will order by the latter as well.

I don't know why PL/SQL and SQL order differently in your test case; maybe (just guessing wildly)
(a) one orders by ROW_NUMBER, the other not or
(b) PL/SQL converts the rowid into a string, then orders the string

You may try experimenting with dbms_rowid.rowid_create.

-- 
Alberto Dell'Era
"Per aspera ad astra"
--
http://www.freelists.org/webpage/oracle-l