Re: TRUTH about ROWID

bpayne@netcom.com (Bob Payne) wrote: > > [Charles Francis (ancrcs4!cfrancis@ancrcs4.attmail.com) wrote] > > > According to the Informix-On-line Dynamic Server Administrators Guide > > Version 7.1 Volume 2 page 43-35 rowid is still supported. As with earlier > > On-line releases rowid is a combination of a page identification number and > > the slot table entry number on the page for nonfragmented tables. Fragmented > > table may have rowid, but; it is implemented differently. > > > The Informix-On-line Dynamic Server Administrators Guide Version 7.1 > > Volume 1 page 16-20 explains the creation of rowid for fragmented tables. > > Rowid is may be created by use of any of the follow clauses: > > WITH ROWID in CREATE TABLE > > ADD ROWID in ALTER TABLE > > INIT in ALTER FRAGMENT > > Any of these implementations of ROWID on fragmented tables adds a 4 byte > > unique value the a table. > > > For more information I suggest finding access to the 7.1 manuals. > > I have not tested it, but I understand that when you do this, you are > actually creating a new column in the table. The rowid is *already* > there! The problem is that it is not unique within the fragmented > table. It *is* unique within the fragment, though. When you add the > rowid as mentioned above you are asking the engine to add a column > which is stored as a serial value. And, as we all know, you can > have only one serial column per table. Yes, this *does* count as the > serial column. > > Long story short: If you add the unique rowid, you cannot have an > additional serial column. > > Can anyone verify this? I don't recall this restriction of only one serial column per table, although I can't say for sure. It would be easy enough to test. I have 2 bad things and a good thing to say about the "new" rowid: Bad: Using a serial column to replace rowid means 4 extra bytes per column and an index (I assume). This means more space and slower access via the new rowid as compared to the old rowid which represented a physical address. Bad: The new rowid hangs around in your program variables along with the rest of the record. I assume you have to set the rowid value to "0" whenever you insert a new record to increment the serial counter. Good: The new rowid should be a more trustworthy way to access tables because the value is not reused after a row is deleted. In fact, unloading and loading a table will recreate the same "new" rowid's.