Re: TRUTH about ROWID
more on this thread:
1. create table bob
(col1 serial, col2 integer)
with rowids
fragment by round robin in rootds, bsi_01, bsi_02, bsi_03;
2. loaded 100,000 rows into this table
3. select rowid,count(*)
from bob
group by rowid
having count(*) > 1;
----> returned 0 rows.
4. ran oncheck -pe and looked for this table. indeed, it did span over
all the dbspaces. it does look very interesting in the report. a
sample of the output looks like:
Disk usage for Chunk 2 Start Length
------------------------------------------- --------- ---------
OTHER RESERVED Pages 0 2
CHUNK FREE LIST PAGE 2 1
* * stuff deleted * *
testing:qads2bsi.bob 250053 8
testing:qads2bsi.system-rowid 250061 16
testing:qads2bsi.bob 250077 8
testing:qads2bsi.system-rowid 250085 8
testing:qads2bsi.bob 250093 8
testing:qads2bsi.system-rowid 250101 8
testing:qads2bsi.bob 250109 8
testing:qads2bsi.system-rowid 250117 8
testing:qads2bsi.bob 250125 8
testing:qads2bsi.system-rowid 250133 8
testing:qads2bsi.bob 250141 8
testing:qads2bsi.system-rowid 250149 8
testing:qads2bsi.bob 250157 8
testing:qads2bsi.system-rowid 250165 8
so:
1. no the WITH ROWID's doesnt count as a serial column
2. rowids ARE unique even across fragments.
again, this is on a sun 2000 running Solaris 2.4 and 7.1 Online.
>
> [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 |
> +------------------------------------------------------------------------+
>
--
regards,
+----------------------------------------------------------------------------+
| . . | Bob Baskett |
| ... ... | Software Engineer |
| ..... ..... | Business Systems Integration Group |
| .. ... .. | Semiconductor Products Sector |
| . . . | Mesa, AZ |
| | President, Informix Users Group Of Arizona |
| Motorola, Inc. | |
+----------------------------------------------------------------------------+
| Sun 690MP 4.1.3, Online 5.01, 4.10.UD1 Tools, Fourgen v4.10.UC1 |
+----------------------------------------------------------------------------+