Re: Rowids in Informix Databases

Art S. Kagel wrote: > On Wed, 13 Oct 2004 12:54:17 -0400, rkusenet wrote: >>Art S. Kagel wrote: >>>Rowids in FRAGMENTED tables are just SERIAL8 datatypes containing unique >>>values, so a row moving around is not an issue. Even in a non-fragmented >>>table this is not a problem - at least not since OL4.10 (IB 4.00 did have >>>this problem though IMS - Jonathan, do you remember?). Anyway, if a row is >>>moved entire to another page because its home page no longer has room for >>>it due to the growth of variable length columns in that and other rows on >>>the page, the original ROWID is still valid as a fowarding pointer to the >>>row's forward page is left behind so that the engine can find it. This is >>>one reason why using VARCHAR and LVARCHAR columns can adversely affect >>>performance over time, as access to all those relocated rows require >>>additional page accesses and possibly additional IOs. >> >> >>if rowid column created WITH ROWIDS is indeed immutable then they can be >>used in queries with the advantage that the query WHERE ROWID = aaaa will be >>very fast (as fast or faster than primary key), even though there is no >>index on that column. Indeed there can be circumstances where they can >>replace surrogate primary keys because we don't have to index ROWID column, >>saving lot of space. > > NO! It is indexed, however, in a fragmented table using ROWID is no better > than using any other SERIAL* column that's indexed. There's nothing special > about ROWID in a fragmented table. YES, in a non-fragmented table you CAN > use rowid for high speed access, however, do NOT use it like a primary key > and save it in related records in other tables! Of you reorg the table the > rowid WILL CHANGE. I just said that it would not change due to row > relocation caused by variable length columns, not that it is immutable! I was summoned? It's a regular SERIAL column, not SERIAL8, not least because it appeared in 7.x which doesn't have SERIAL8 type. ROWIDs are moderately stable in all versions of IDS. Moderately, because they don't change when you simply do an UPDATE. If the row needs to move, the ROWID remains unchanged, but the slot in the original page becomes a forwarding pointer to the new page. As far as I recall, even OnLine 4.00 (the first version with VARCHAR type - Turbo, the predecessor version, did not have VARCHAR) did row pointer forwarding, but I might be misremembering. In any system with non-fragmented tables (virtual ROWID), any reorganization of the table can change the rowids. You should never store the ROWID in a database - it is unreliable. Any ALTER TABLE could reassign ROWID values; so could ALTER INDEX <name> TO CLUSTER. With fragmented tables, the physical ROWID column is distressingly stable - in fact, its stability is too great and people get confused when working with the virtual ROWID columns. As Art points out, the WITH ROWIDS clause has a physical ROWID column (albeit one which is not returned unless you request it), and it is indeed indexed. It is as fast as an INTEGER or SERIAL primary key, but not faster. -- Jonathan Leffler #include <disclaimer.h> Email: jleffler@earthlink.net, jleffler@us.ibm.com Guardian of DBD::Informix v2003.04 -- http://dbi.perl.org/