Re: Comparing two rowids
- From: "Alberto Dell'Era" <alberto.dellera@xxxxxxxxx>
- To: contactarul@xxxxxxxxx
- Date: Tue, 28 Nov 2006 23:06:43 +0100
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"
--
//www.freelists.org/webpage/oracle-l
Other related posts:
