How does order by row id work in Oracle?

Question

I have a table called points. I executed the following query and expected a list of lexicographicaly sorted list of ROWIDs but that did not happen. How does Order by rowid sorts the row?

select rowid from points order by rowid

I had rows like following

1. AAAE6MAAFAAABiSAAA
2. AAAE6MAAFAAABi+AAA

2nd row is lexicographicaly smaller than first row. So what is sorting criteria if it is not lecxicographical sorting?

Answer 1

Why you see is only a representation used for display purposes.

The actual rowid contains binary information about the data block, the row in the block, the file where the block is located and the internal object id of the table (See the manual for details)

When you use order by rowid Oracle sorts the rows based on that (internal) information, not based on the "string representation".

If you change your query to:

select rowid, 
       dbms_rowid.rowid_relative_fno(rowid) as rel_fno,
       dbms_rowid.rowid_row_number(rowid) as row_num,
       dbms_rowid.rowid_block_number(rowid) as block_num, 
       dbms_rowid.rowid_object(rowid)
from points 
order by rowid

You will most probably see the logic behind the ordering of the rownumber.

Note that the value for dbms_rowid.rowid_object() will always be the same. And if you only have two rows in your table, both will most probably also have the same value for rowid_block_number()