Using ROWID in the WHERE clause of a SELECT statement

[miamirocks]

hi all,

my team is trying to select a value from a table using the rowid as the selection criterion

e.g. select column_name from table_name where rowid > 'AAA112BBBCCC12A'

this query does not appear to return accurate results e.g. it return rows instead of two. my questions are:

1. is this a legitimate approach?
2. should we convert the row id to varchar2? if so how should this be done?

thanks!

 

[Turkbear]

Hi,
How are you determining what ROWIDs to ask for?

Please explain what you mean by:
this query does not appear to return accurate results e.g. it return rows instead of two

Also there are at least 2 considerations to keep in mind if using in a Where clause:

Usually, a rowid value uniquely identifies a row in the database. However, rows in different tables that are stored together in the same cluster can have the same rowid.

If you delete and reinsert a row with the Import and Export utilities, for example, then its rowid may change. If you delete a row, then Oracle may reassign its rowid to a new row inserted later.






To Paraphrase:"The Help you get is proportional to the Help you give.."

 

[Beilstwh]

A ROWID IS A UNIQUE IDENTIFIER FOR A PARTICULAR BLOCK. When you use the select

select column_name from table_name where rowid > 'AAA112BBBCCC12A'

you are saying return every rowid that has a higher block id(file,datafile,block #) then the rowid I specify. Oracle doesn't store new rows in ever higher blocks, it stores them wherever there is space in the tablespace. If might return rows that were earlier or later then the one you specified. The ONLY way to specifically identify new rows is to use a column containing a timestamp or a sequential increasing number. Using rowid's is not only incorrect, it will fail. Even using rowid to return to the same row is not always correct. An update or rebuild could easily change the rowid.

Bill
Oracle DBA/Developer
New York State, USA

 

[stefanhei]

1. Please explain what goal you try to archive with your select, but in most cases this is no valid approach. I would reject to put such statements in any stored code.
2. If you have to deal with rowids, maybe to analyze a deadlock or any other ad hoc task, you may use the hextoraw or rawtohex functions, but as Turkbear and Bill already explained there are some things to keep in mind.

Stefan