ROWID in DB2…
The Row id is part of every successful database in today’s world.
In DB2 every record stores a RID in the form of hex values. ROWID is basically a combination of page(block),slot (within a page),and file. Fetching the records using ROWID’s is amongst the most optimized method and is used when indexes are used in the predicates. In fact, ROWID is the fastest way of retrieving records.
ROWID is internally generated unique row identifier which is constant and immutable unless a REORG is done.
In DB2 ROWID is of 4 bytes(3 bytes for page and 1 for slot).Since 1 byte is 8 bits and 8 bits can go up to 255,therefore one page in DB2 can contain 255 records and thats why we have the limitation of MAXROWS as 255 per page.
ROWID can be used separately in DB2 by adding a column of ROWID data type.
This column can then be retrieved in a Select and stored in a program's host variable. It can later be used to directly retrieve the row in the table to perform a Select, Update, or Delete. When using a Rowid in this way, a row is directly accessed without performing a Table scan and without needing to use an Index.
Select * from table_name where rowid_column = :hostVarRowid;
In DB2, ROWID is actually a data type of a column.
Cheers!!!
Are you sure that RID = ROWID? I found this in an IBM RedBook:
ReplyDeleteA ROWID is a new data type and is not the same as a record identifier
(RID), which is internally used by DB2 to reflect the position of a row in a table. But you can
find the RID as a part of the externalized ROWID when you select the ROWID column.
[http://www.redbooks.ibm.com/redbooks/pdfs/sg246571.pdf]
Thank you for clarifying everything for me. Index fetches using ROWID (or RID, the difference is academic) and returns a row only. Index itself is a list of RIDs unless you are talking about cluster indexes.
ReplyDeleteOnly question is does db2 fetch a whole page to reach 1 row ?