Re: Help on rowid (SE 5.0) - Reminder

On Feb 6, 6:12pm, Jonathan Leffler wrote: } Subject: Re: Help on rowid (SE 5.0) - Reminder } Hi, Jim, } } >> >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. Sorry Jonathan. I had completely forgotten that ROWID on a fragmented table was a generated construct specified when fragmenting the table to maintain backward compatability. You are right, as they are now just a serial field, they don't change as the row moves between fragments. Also if you attempt to access ROWID on a fragmented table that has not had ROWID specified you get an error. Serves me right for working late and thinking I didn't need to look in the manual!! 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!