Re: Rowids in Informix Databases

Warning: If you have in place alter table outstanding on your table the rowid can change!!!!!!!!!!!! So rowids is a don't do this!!! See you Superboer. "Art S. Kagel" <kagel@bloomberg.net> wrote in message news:<pan.2004.10.13.17.40.17.32958.1355@bloomberg.net>... > 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! > > Art S. Kagel