Re: Help on rowid (SE 5.0) - Reminder

Hi, Jim, I quibble with your conclusions near the bottom of this message. I've edited out portions of the original messages, but it is difficult to do much editing and still leave a newcomer to the thread with enough context to make sense of it. }From: Jim Gordon <jgordon@us.DHL.COM> }Date: Tue, 6 Feb 1996 17:41:30 -0800 } }On Feb 5, 6:17pm, Jonathan Leffler wrote: }> Errmmm... }> }> >From: tonytd@ttyrwhit.demon.co.uk (Tony Tyrwhitt-Drake) }> >Date: Mon, 05 Feb 1996 23:52:52 GMT }> >X-Informix-List-Id: <news.20939> }> > }> >In article <4f54f0$hic@cssun.mathcs.emory.edu>, javi@psi.ernet.in }> >(javeed anwart) says: }> >>[...]? I want to know because }> >>I doubt since the rowid's are not physically deleted it may degrade the }> >>performance in the second case. }..... }> > }> >A rowid is unique for the life of a table. If you drop the table }> >and reload it a particular row of data may have a new rowid. Otherwise }> >rowids start counting up from 1 and are never reused. }> }> Not so. Not so at all! }> }> ROWIDs correspond to a physical slot in the data storage area, in both }> OnLine and SE. In SE, it corresponds to the record number in the .dat }> file; in OnLine, it corresponds to a page and slot within page. While }> a particular row of data continues to exist, the ROWID does not change. }> Once a row is deleted, the physical slot is available for re-use, and }> some other row can be given the same ROWID. This is why you should NEVER }> store a ROWID in a permanent table -- what was valid as a cross-reference }> at time A may still be valid at time B, but might point to a completely }> different row. Note that ALTER TABLE and ALTER INDEX can revise the ROWIDs }> completely. }... }> >A rowid is just a unique key given to each row. }..... }> A ROWID is unique at any given time, but the ROWID is not guaranteed not to }> change over the life of a table, and especially not across a table }> reorganization. It is guaranteed not to change while a row continues to }> exist. } }I want to support and agree with everything that Jonathan has said in reply to }this issue except for his last sentence immediately above. I believe that now }it is not even guaranteed that a rowid won't change during the life of a row. }With version 7.1 Online updating a row without changing its primary and unique }keys can still result in the rowid changing if the table is fragmented and the }fragmentation algorithm is based on the attribute or attributes that the update }does change. The update can then cause the row to move between fragments based }on the calculation of which fragment it should be in so changing its rowid. I was not considering fragmented tables, so my answer should be construed as applying to 6.0x and prior versions of OnLine, and to all versions of SE in existence. For non-fragmented tables at 7.10 and later, a ROWID is still a virtual column, the same as previously, so my answer also applies to non-fragmented tables in all versions of OnLine in existence. Unless a fragmented table is created with the WITH ROWIDS attribute, it does not have a ROWID so the whole discussion is irrelevant to them. I haven't done the necessary test, but my understanding is that the ROWID does not change in a fragmented table WITH ROWIDS when a row is relocated from one fragment into another. Bear in mind that for a fragmented table WITH ROWIDS, the ROWID is a physical column, not the virtual column which we are all used to thinking about, and that fragmented tables do not have a virtual ROWID column ever. Further, that ROWID is unique across all fragments of the fragmented table -- that is its purpose in life. So, I see no reason why the ROWID of a row would change, even if the fragment in which it is located changes. Note that there is an internal <fragid, rowid> pair which would change when the row is moved between fragments, but this is not visible to the user, and should not affect the physical ROWID column. }Strictly speaking you can argue that this is no longer the same row. I can, and would, argue that within the meaning of the term, it is still the same row. And I believe the Engine would support me, but (as I said previously) I have not conducted the necessary test to verify this. }But I think most ordinary users in the field would generally consider it }to be the same row. So I side with most ordinary users. }Jonathan is that correct? Not entirely, I think, but my answer never even considered fragmented tables, so I was remiss. Yours, Jonathan Leffler (johnl@informix.com) #include <disclaimer.h>