1

As per my design I want to fetch rowid as in

select rowid r from table_name;

into a C variable. I was wondering what is the max size / length in characters of the rowid.

Currently in one of the biggest tables in my DB we have the max length as 18 and its 18 throughout the table for rowid.

Thanks in advance.

Edit: Currently the below block of code is iterated and used for multiple tables hence in-order to make the code flexible without introducing the need of defining every table's PK in the query we use ROWID.

select rowid from table_name ... where ....;

delete from table_name where rowid = selectedrowid;

I think as the rowid is picked and used then and there without storing it for future, it is safe to use in this particular scenario.

Please refer to below answer: Is it safe to use ROWID to locate a Row/Record in Oracle?

I'd say no. This could be safe if for instance the application stores ROWID temporarily(say generating a list of select-able items, each identified with ROWID, but the list is routinely regenerated and not stored). But if ROWID is used in any persistent way it's not safe.

pOrinG
  • 896
  • 3
  • 13
  • 27
  • 1
    Length of rowid does not change throughout the DB. Varchar2 representation of it is always 18 in length for DB12c, I don't have the chance for 11g, but suppose should have length of 18, also. – Barbaros Özhan May 29 '18 at 13:24
  • 1
    The length and format changes from release to release. Are you using Pro*C? There is an `OCIRowid` type provided for storing universal row ids. That's the proper way to handle them, as far as I know. – Matthew McPeak May 29 '18 at 13:29
  • IMHO for OCI you have to distinguish between ROWID a UROWID (for IOT TABLES). ROWID contains datafile number, block number, row number. Create a database having zilloon of datafiles and you will see. ROWID can have up to 104B, while UROWID can have up to 4K. – ibre5041 May 29 '18 at 13:31
  • possible duplicate of https://stackoverflow.com/questions/29045104/is-length-of-rowid-in-oracle-11g-fixed – wolφi May 29 '18 at 13:42
  • @wolφi I actually read that question and it didn't clear my doubts hence I posted this question.. – pOrinG May 29 '18 at 13:45
  • @MatthewMcPeak Thanks for the insight, Yes I am using proc however I just read a docx from oracle stating that unless i am going to create index-organized tables I can just use 19 characters [18+ null terminator] in proc. Currently my app uses 100 and I don't want to change anything unless absolutely necessary. In future however I will use OCIRowid. Link : https://docs.oracle.com/cd/B14117_01/appdev.101/a97269/pc_04dat.htm#i23634 – pOrinG May 29 '18 at 13:48
  • 2
    can you explain why you want/need ROWIDs for your application design? – thatjeffsmith May 29 '18 at 13:49
  • @BarbarosÖzhan Thank you for the comment. I think its the same in 11g as well. – pOrinG May 29 '18 at 13:49
  • you're welcome. – Barbaros Özhan May 29 '18 at 13:53
  • @thatjeffsmith Just want to identify that row later in the workflow. – pOrinG May 29 '18 at 13:59
  • @pOrinG and you don't have a UK or PK in your data model? ROWIDs can change... https://asktom.oracle.com/pls/apex/asktom.search?tag=when-can-a-rowid-change – thatjeffsmith May 29 '18 at 14:08
  • @thatjeffsmith check my edit please. – pOrinG May 29 '18 at 14:15

2 Answers2

2

A physical ROWID has a fixed size in a given Oracle version, it does not depend on the number of rows in a table. It consists of the number of the datafile, the number of the block within this file, and the number of the row within this block. Therefore it is unique in the whole database and allows direct access to the block and row without any further lookup.

As things in the IT world continue to grow, it is safe to assume that the format will change in future.

Besides volume there are also structural changes, like the advent of transportable tablespaces, which made it necessary to store the object number (= internal number of the table/partition/subpartion) inside the ROWID.

Or the advent of Index organized tables (mentioned by @ibre5041), which look like a table, but are in reality just an index without such a physical address (because things are moving constantly in an index). This made it necessary to introduce UROWIDs which can store physical and index-based ROWIDs.

Please be aware that a ROWID can change, for instance if the row moves from one table partition to another one, or if the table is defragmented to fill the holes left by many DELETEs.

wolφi
  • 8,091
  • 2
  • 35
  • 64
  • Please don't take offence, we were just worried. Are you in control of the table structure, can you prevent that one of the tables is Index organized (IOT)? – wolφi May 29 '18 at 14:21
  • Oh don't worry, I didn't find it offensive at all. Its better if everyone tries to eliminate all possible cases so I can sleep in peace :). Yes, currently we have no IOT tables and we won't have the same in the future as well. – pOrinG May 29 '18 at 14:29
1

According documentation ROWID has a length of 10 Byte:

Rowids of Row Pieces

A rowid is effectively a 10-byte physical address of a row.

Every row in a heap-organized table has a rowid unique to this table that corresponds to the physical address of a row piece. For table clusters, rows in different tables that are in the same data block can have the same rowid.

Oracle also documents the (current) format see, Rowid Format

In general you could use the ROWID in your application, provided the affected rows are locked!

Thus your statement may look like this:

CURSOR ... IS
select rowid from table_name ... where .... FOR UPDATE;

delete from table_name where rowid = selectedrowid;

see SELECT FOR UPDATE and FOR UPDATE Cursors

Oracle even provides a shortcut. Instead of where rowid = selectedrowid you can use WHERE CURRENT OF ...

Community
  • 1
  • 1
Wernfried Domscheit
  • 54,457
  • 9
  • 76
  • 110
  • Thanks for the answer and the idea of FOR UPDATE; however as per the design we are trying to delete unnecessary rows with this query hence it doesn't make sense to use FOR UPDATE. in this case as no other service,user,process is going to use those rows. – pOrinG May 29 '18 at 17:36
  • That doesn't mean it is a 10 character string though. See https://docs.oracle.com/database/121/SQLRF/functions171.htm#SQLRF06101 – Matthew McPeak May 29 '18 at 21:23
  • `FOR UPDATE` does not necessarily mean the rows will be *updated* - it means the rows are **locked** in order to prevent any changes of their ROWID. – Wernfried Domscheit May 30 '18 at 07:12
  • @WernfriedDomscheit Thanks for the explanation. So ROWID usually updates rarely mostly either a alter is issued to the table or the record is updated [not 100% sure]. However as during my process the table will never be altered or the records I am trying to delete will never be touched its safe to say that the `ROWID` will remain the same. However your answer is going to be of great help when trying to use `ROWID` in some other scenarios – pOrinG May 30 '18 at 11:19
  • 1
    Typically the ROWID does not change, see documentation I put in answer. However, in order to a have a save and reliable application you should lock the rows (or the entire table which can have a negative impact on over all performance) – Wernfried Domscheit May 30 '18 at 11:54
  • I agree. We should to cover all the bases even if there is a slight chance of it happening. So now I have another doubt actually about the interaction of FOR UPDATE when used in a cursor in proc where the records are fetched in batches. Because the code will be something like.. `Open Cursor as Select xyz FOR UPDATE.. Loop ..Update x records; commit; End Loop.` – pOrinG May 30 '18 at 13:52
  • Please open a separate question for that. Just one note: `COMMIT;` releases the locks, thus you must not put it inside the loop. – Wernfried Domscheit May 30 '18 at 14:58
  • @WernfriedDomscheit Please advise on https://stackoverflow.com/questions/50608708/interaction-of-select-for-update-lock-with-cursor-and-batch-dml – pOrinG May 30 '18 at 15:48