Re: Help on rowid (SE 5.0) - Reminder

On Feb 5, 6:17pm, Jonathan Leffler wrote: } Subject: Re: Help on rowid (SE 5.0) - Reminder } 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 anwar t) says: } > } >>What I want to know is : } >> } >>Suppose I do a partial scan or total scan on this table before and after } >>deletion, how will be the performance of the system? 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. Strictly speaking you can argue that this is no longer the same row. But I think most ordinary users in the field would generally consider it to be the same row. Jonathan is that correct? } Yours, } Jonathan Leffler (johnl@informix.com) #include <disclaimer.h> }-- End of excerpt from Jonathan Leffler Cheers - Jim -- ----------------------------------------------------------------------------- Jim Gordon DHL Airways Inc. jgordon@us.dhl.com ----------------------------------------------------------------------------- My opinions are my own. They may vary with time but they remain mine!