Re: TRUTH about ROWID

There seems to be some confusion about this rowid thing, so here's the truth (as I see it!)... create table tab1(f1 serial, f2 char(10)) with rowids fragment by round robin in dbs1, dbs2; This creates a table with a special column that is a serial column. This column is called rowid. You can select it, do where clauses with it, just like the old rowid you've come to know and love. The difference is this: Access to a row based on a rowid is now via an index. And it is unique across all fragments (it has to be, otherwise what would be its value?) And you can have the rowids and another serial column. create table tab2(f1 serial, f2 char(10)) fragment by round robin in dbs1, dbs2; This will create a fragmented table. Selecting the rowids from this table will yield the following error: 857: Rowids do not exist on table. There is no usable rowid column on this table. We do maintain rowids for this table, but they are not unique across all fragments, but are unique within each fragment. Creating a table with no fragmentation gives you a rowid that works just like the rowid from v5.x and 6.x. Hopefully this will clear up some of the confusion out there about rowid and fragmented tables. ---------------------------------------------------------------------------- Bill Glidden Sr. Advanced Support Engineer E-mail: glidden@informix.com Advanced Support (415) 926-6904 Informix Software, Inc. ---------------------------------------------------------------------------- Bob Payne (bpayne@netcom.com) 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? : +------------------------------------------------------------------------+ : | Bob Payne Internet: bobp@dev.dsc.dalsys.com | : | Systems Engineer : bpayne@netcom.com | : | Dallas Systems Corporation Voice: (214) 383-2238 | : +------------------------------------------------------------------------+