rowid no longer unique?
Ron
I think I remember seeing something in this bb about rowid no longer being
necessarily unique within a table for v7. Is this true? More than once,
while coding with ESQL, I coded a singleton select that I knew would return
more than one row so I just added a where clause to avoid opening a cursor:
select col from tab where rowid =
(select max(rowid) from tab where key = "somevalue")
Some purists might say that the model is not properly constructed if I have
to do this but sometimes when you are dealing in the real world, well....
So, is it true? Thanks in advance.
Ron Cichoski
rcic...@adventcon.com
Mike Segel
Ron wrote:
>
> I think I remember seeing something in this bb about rowid no longer being
> necessarily unique within a table for v7. Is this true? More than once,
> while coding with ESQL, I coded a singleton select that I knew would return
> more than one row so I just added a where clause to avoid opening a cursor:
>
> select col from tab where rowid =
> (select max(rowid) from tab where key = "somevalue")
Well, Not being a purist, I would consider a cursor.
If you frag a table for pdq, rowid is not unique.
IMHO, if you are going to write code, write it so that it is
maintainable. Rowid is no longer a *guaranteed* method of selecting a
single row.
Now for $.02 worth, heres another tip from your uncle mike:
Use a cursor and have the select statement do an order by so that
your first row is the row you want.
Declare the cursor.
Open the cursor.
fetch the row.
Close the cursor.
Go one with your program or Free the cursor.
If this select statment is being used multiple times, it is better
to use a cursor. If this statement is being used in one part of the
program and is used infrequently, use a cursor since the overhead
is minimal.
-Mike
Cheryl Kendricks
I'm one of those puriest!!! IF Informix would have never allowed this in
the first place, this probally would not have happend in the Real
World....
I say this is the time to "GET the Model Corrected" if at ALL possible
(cost of correctting this NOW, will be cheper then trying to fix data and
code further down the road).
But anyway Informix being the Nice Cust company did allow the option of
building tables with the "ROWID" column. Check it out in the 7.x doc for
table creation. What other things you must consider and how it works I'm
not clear on.... since as DBA at my site I making this Number on the PGM
list of this to change in going from 5 to 7. Yet as you say in the REAL
WORLD everyone may not be able to correct this!!!!
Hopes this is of some Help!
-------------------------------------------------------------------------
Cheryl Kendricks
Internet:che...@prod1.jcdc.doleta.gov OR che...@gwysmtp.jcdc.doleta.gov
DTSI, Inc. Voice: 1-800-598-5008 Fax: 512-393-7296
Database Administrator - DOL Job Corps San Marcos, Texas
------------------------------------------------------------------------
On 31 May 1996, Ron wrote:
> I think I remember seeing something in this bb about rowid no longer being
> necessarily unique within a table for v7. Is this true? More than once,
> while coding with ESQL, I coded a singleton select that I knew would return
> more than one row so I just added a where clause to avoid opening a cursor:
>
> select col from tab where rowid =
> (select max(rowid) from tab where key = "somevalue")
>
Robert Minter
Mike Segel writes:
*
* Ron wrote:
* >
* > I think I remember seeing something in this bb about rowid no longer being
* > necessarily unique within a table for v7. Is this true? More than once,
* > while coding with ESQL, I coded a singleton select that I knew would return
* > more than one row so I just added a where clause to avoid opening a cursor:
* >
* > select col from tab where rowid =
* > (select max(rowid) from tab where key = "somevalue")
*
* Well, Not being a purist, I would consider a cursor.
I too would consider this.
* If you frag a table for pdq, rowid is not unique.
This is not correct. If the table is frag'ed, there is no longer a ROWID
field unless you "ALTER TABLE WITH ROWIDS".
* IMHO, if you are going to write code, write it so that it is
* maintainable. Rowid is no longer a *guaranteed* method of selecting a
* single row.
This might possibly be, but with our 2 million record test, both with
unfrag'ed and frag'ed tables, ROWID stayed unique.
* Now for $.02 worth, heres another tip from your uncle mike:
* Use a cursor and have the select statement do an order by so that
* your first row is the row you want.
*
* Declare the cursor.
* Open the cursor.
* fetch the row.
* Close the cursor.
* Go one with your program or Free the cursor.
*
* If this select statment is being used multiple times, it is better
* to use a cursor. If this statement is being used in one part of the
* program and is used infrequently, use a cursor since the overhead
* is minimal.
This, definitely, is the answer for the database that are not relational.
By this, I mean, where tables are based on a unique key identifier to
retrieve the data. Don't get me wrong, or software falls short of being
truely relational. But, then again, whose doesn't?
Thanks,
-----------------------------------------------------------------------------
Robert Minter Data Systems Support
Senior Software Engineer A Client Technologies Company
E-Mail: r...@dssmktg.com Tel: 714.771.0454
Orange County Emmaus Community Fax: 714.771.3028
De Colores - Walk# OC-13 http://www.dssny.com
Jonathan Leffler
It is not quite so simple as ROWID is no longer unique.
If a fragmented table is created, then (by default) there is no ROWID
available at all on that table. This is because if it was made available,
it would not be unique -- the same rowid could exist in each of the
dbspaces into which the table was fragmented. (Internally, the ROWIDs are
stored with a FRAGID to identify the fragment in which the data is stored,
but the FRAGID is not available externally.) However, you can also create
a fragmented table WITH ROWIDS, in which case there is a physical column
(as opposed to the traditional virtual column) called ROWID which cannot be
updated. The values in this column are guaranteed to be unique for the
whole table -- at the expense of 4-bytes per row, some processing during
INSERT, plus an index on the ROWID column (which is probably a
non-negligible overhead if the table is big enough to need fragmenting).
So, you should avoid using ROWID in your code because it won't work for
every table in OnLine 7.x and above.
Note that SE is not affected, and neither are non-fragmented tables, so you
have to go out of your way to break your code by making fragmented tables.
A simple upgrade from, say, 5.0x OnLine will not break your programs.
Yours,
Jonathan Leffler (jo...@informix.com) #include <disclaimer.h>
>From: RonCi...@msn.com (Ron )
>Date: 31 May 96 21:44:23 -0700
>X-Informix-List-Id: <news.24511>
>
>I think I remember seeing something in this bb about rowid no longer being
>necessarily unique within a table for v7. Is this true? More than once,
>while coding with ESQL, I coded a singleton select that I knew would return
>more than one row so I just added a where clause to avoid opening a cursor:
>
> select col from tab where rowid =
> (select max(rowid) from tab where key = "somevalue")
>
Cheryl Kendricks
A simple upgrade with a DBA like me creating tables that are Fraggmented
will break your code! So the best advice STOP USING ROWID in APPLICATION CODING!!!
I just had to say this!!!!
-------------------------------------------------------------------------
Cheryl Kendricks
Internet:che...@prod1.jcdc.doleta.gov OR che...@gwysmtp.jcdc.doleta.gov
DTSI, Inc. Voice: 1-800-598-5008 Fax: 512-393-7296
Database Administrator - DOL Job Corps San Marcos, Texas
------------------------------------------------------------------------
On Mon, 3 Jun 1996, Jonathan Leffler wrote:
} It is not quite so simple as ROWID is no longer unique.
} .......
June Tong
Mike Segel (mi...@segel.com) wrote:
: Ron wrote:
: >
: > I think I remember seeing something in this bb about rowid no longer being
: > necessarily unique within a table for v7. Is this true? More than once,
: If you frag a table for pdq, rowid is not unique.
Actually, I believe that if you fragment a table without adding the WITH
ROWIDS (which basically adds a serial column), you can no longer select
rowid at all (uniqueness then is not even a factor).
June
---- June Tong Informix Software ----
---- Senior Consultant (415) 926-6140 ----
---- International Support ju...@informix.com ----
---- Location-du-jour: Menlo Park ----
*
* Standard disclaimers apply
*
- Please do not send me requests/questions by mail. When I have the knowledge
- and time permits, I try to answer questions on comp.databases.informix, but
- travel schedule, time, and volume make responding to personal requests
- difficult and often slow. Please call your local Informix Technical Support
- organization for assistance with technical issues.