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),
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
(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"

Other related posts: