Re: rowid's changing

Andreas Zeugswetter (andreas.zeugswetter@telecom.at) wrote: : David Williams wrote: : > In article <5rqe5r$aq6@cssun.mathcs.emory.edu>, Scott Black : > <sblack@elsouth.com> writes : > >I have a table (nothing special; not fragmented or anything) that = : > >changes rowid's whenever I update a row. : > > : > >For instance I'll have two screens up. The first running isql query : > -> = : > >select rowid, * from table where key_field =3D "key_value" The : > second = : > >running sperform on the same table. Using just the normal update = : > >function when I hit escape, if I re-run the query from screen one the : > = : > >rowid is now changed. Also if I hit update in screen two again I get : > an = : > >error (100), I believe because isql has now lost track of the row. : > =20 : > > : > >I have been talking with technical support and they have never heard : > of = : > >this. Also we are unable to re-create it. Therefore they can't : > really = : > >do anything to fix the problem. I'm sure I can fix this instance of : > the = : > >problem by dropping and re-creating the table, but this does not : > address = : > >the larger problem; rowid's should NEVER change. : > > : > >Has anyone else experienced this? : > > : > Does the table contain varchars/nvarchars which you are maiking : > larger? : > : > A table schema and copy of the form would be nice.. : > : > >Thanks in advance : > > : > >PS Please no lectures on how the use of rowid's is not supported. I : > am = : > >aware of the implications of using rowid's, I'm more concerned about : > why = : > >the engine is doing this and if it might be a symptom of larger : > problems = : > >to come. : > : > -- : > David Williams : Well, this seems simple to me ! : rowid's in informix are some kind of physical address of a row. From it : you can directly find the physical page and slot of a row. Therefore : whenever the storageplace (page) of a : row changes, so does it's rowid. Otherwise, to find a row by rowid, you : would need : page-chaining like Oracle, or an Index for rowid's (I think both is not : optimal). Not exactly true...You are correct that rowid refers to a physical location, but... Assuming non-fragmented tables, a rowid should never change for a row under any circumstance. If the row size changes due to the update of a varchar field, the row may move, but online leaves behind a forwarding pointer to the new location. The initial access of the row is to the forwarding pointer at the original location, so the rowid hasn't changed. : Therefore rowid's cannot be used as object id's, unless you have: : 1. no cluster index (unless never reorganized) : 2. no reorganization : 3. no variable size columns, that get enlarged by updates : Hope this helps : Andreas Zeugswetter