Oracle ROWID values

Question

I was trying to get the last record from a table using the following query.

SELECT *
  FROM TABLE1
 WHERE ROWID IN (SELECT MAX (ROWID) FROM TABLE1);

When I checked the ROWID,

The value is AAAZA5eAFAAA7AAAA

And some other value for MIN(ROWID).

How this pseudocolumn is getting evaluated here? It has also mentioned that Oracle Database does not guarantee that the values of such columns are valid rowids.So ROWID values are not necessarily unique within a schema?

As per the doc: Each row in the database has an address. You can examine a row address by querying the pseudocolumn ROWID. Values of this pseudocolumn are strings representing the address of each row. These strings have the datatype ROWID. You can also create tables and clusters that contain actual columns having the ROWID datatype. Oracle Database does not guarantee that the values of such columns are valid rowids.

score 5 · Accepted Answer · edited May 23 '17 at 11:57

You cannot get the last row in a table by this method. A rowid can change, which means it will not be consistent and Oracle never guarantees that they're created in any sort of order.

The only reliable method of obtaining the last row of a table is to use the data in that table.

To actually answer your question the psuedocolumn is being evaluated in a manner consistent with your NLS_SORT parameter:

select value
  from nls_session_parameters
 where parameter = 'NLS_SORT';

VALUE
------------------------------
BINARY

In my case it's binary, which means that the database is performing a binary sort in order to work out whether any one value is "greater" than another. Think of it as if it were the ASCII table; the decimal ASCII value for A is 65 and the value for u is 117. This means that A is sorted above u.

Oracle ROWID values

1 Answers1

Linked