Description of ROWID
The poster asked what Informix's ROWID actually is and whether it could serve as a unique key for rows held in a client-side result set/grid. Respondents explained ROWID is a pseudo-column holding the row's logical address (page number plus slot), which is why values aren't consecutive, can't be indexed, and isn't guaranteed unique in fragmented tables unless the table is created WITH ROWIDS. It can also change (in-place ALTER TABLE, clustering an index, row relocation). Consensus advice: don't rely on it; store the table's real primary key, or use FOR UPDATE cursors with WHERE CURRENT OF for updates/deletes.
Auto-generated by DrWatson from the posts below — may be imperfect; read the full thread.
Hi, does anyone have some background information about the ROWID column? Can I used it as primary key for temporary result sets on the client side? Why are the nummbers not successive? TIA Joachim Engel
Joachim Engel wrote: > Hi, > > does anyone have some background information > about the ROWID column? > > Can I used it as primary key > for temporary result sets on the client side? > Why are the nummbers not successive? > > TIA > Joachim Engel > > The manual has a very good description about it: Don't use it! Don't rely on it! It's an internal engine id. It's not successive because the inserts aren't necesseraly successive. And then there are fragmented tables... the rowid is only guaranteed to be unique within a fragment... unless you use the "WITH ROWIDS" clause... Regarding IN PLACE ALTER TABLE, when you do an update in a table wich has a pending in place alter table the row ROWID may change (the row may be re-allocated). So, forget about it... This is my personal advice and is more or less the official advice. Regards.
"Joachim Engel" <joachim.engel@engel-edv.de> wrote in message news:<c21rd3$tv7$06$1@news.t-online.com>... > Hi, > > does anyone have some background information > about the ROWID column? > > Can I used it as primary key > for temporary result sets on the client side? > Why are the nummbers not successive? > > TIA > Joachim Engel The rowid is a pseudo column with describes the logical address of the row with respect to the tablespace. It consists of three bytes for the relative page number and one byte for the slot number within the page. No - since it is not a real column, you can not build an index on it and thus can not use it as a primary key. The rowid is not consecutive because the incremental value of it depends on how many rows are currently on a page and how many rows can physically fit on a page. You should not consider the rowid as persistant. It is possible for the rowid for a row to change, as in the case of an inplace alter or reclustering of an index column.
Hi, thank you very much for your informative answers. Never fear! We don't want use ROWID as primary key for manipulations on the database. We just need a unique id for each row in our client result set in a grid or in memory. No any other use! <g> Kind regards Joachim Engel
Joachim Engel wrote: > Hi, > > thank you very much for your informative answers. > > Never fear! > We don't want use ROWID as primary key > for manipulations on the database. > We just need a unique id for each row > in our client result set in a grid or in memory. > No any other use! <g> > > Kind regards > Joachim Engel > > Then to reiterate what has already been said: * Do not use the ROWID * If you really want to use it be aware that it changes * whenever a row moves on disk - alter table, update of varchar, ... * be aware that it is not unique in fragmented tables (unless WITH ROWID is specified) I'm not proud but I've successfully used them. -- Rob Wilson rob_wilson_at_ameritech.net (replace _at_ with @)
Joachim Engel wrote: > > Never fear! > We don't want use ROWID as primary key > for manipulations on the database. > We just need a unique id for each row > in our client result set in a grid or in memory. > No any other use! <g> Even momentarily, if you update a row that you have a rowid for, it's rowid might change. It's not smart and will probably burn you at the worst possible moments. Store the real PK of the table instead, or use the FOR UPDATE clause on cursors and then use the "WHERE CURRENT OF" clause on UPDATE and DELETE statements.
Andrew Hamm wrote: > Joachim Engel wrote: > >>Never fear! >>We don't want use ROWID as primary key >>for manipulations on the database. >>We just need a unique id for each row >>in our client result set in a grid or in memory. >>No any other use! <g> > > > Even momentarily, if you update a row that you have a rowid for, it's rowid > might change. Not if you update it - that is one time it won't change (think forwarding pointers). However, it could change if someone else reorganized the table (eg a non-IPA alter table, or ALTER INDEX n TO CLUSTER). OTOH, that would probably invalidate your cursors (-710 - ick). > It's not smart and will probably burn you at the worst possible moments. Yes. > Store the real PK of the table instead, or use the FOR UPDATE clause on > cursors and then use the "WHERE CURRENT OF" clause on UPDATE and DELETE > statements. Yes. -- Jonathan Leffler #include <disclaimer.h> Email: jleffler@earthlink.net, jleffler@us.ibm.com Guardian of DBD::Informix v2003.04 -- http://dbi.perl.org/