Re: Rowids in Informix Databases

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.