Re: Rowid Question
Exhibit A:
>From: uunet!copper.denver.colorado.edu!klonz (K Lonz)
>Subject: Rowid Question
>Date: 26 Sep 92 16:11:09 GMT
>X-Informix-List-Id: <news.1885>
>
> If you 'load' a new table from a previously 'unloaded' file
>are the rowids always reassigned sequentially starting with 1? (i.e.,
>each added record will have rowid numbers 1,2,3,4,...,n).
Exhibit B:
>From: uunet!mt2.BELL-ATL.COM!dksl2vq (Rory Martin)
>Subject: Re: Rowid Question
>Date: Sun, 27 Sep 92 20:27:49 EDT
>X-Informix-List-Id: <list.1483>
>
>> ...
>
>The simple answer is...yes. Rowids are simply physical positions within
>the table. They have no other relationship with a record. They are also
>reassigned within the same table if a record has been deleted and another
>inserted.
The simple answer isn't as simple as that -- it depends!
If you are using OnLine, the rowid consists of a page number and a slot number
and the lowest value will be something like 0x201.
If you are using SE, and if you never deleted anything from the table,
and if you inserted the data in the order in which it is unloaded, and you drop
the table and build a new one from scratch, then you will normally find that
the rowids are indeed in sequence. If any of these conditions isn't met,
all bets are off.
You should only use rowid transiently -- it should never be relied on from a
run of one program to the next run of the same program, or any run of any
other program. It may not even be safe to rely on it within a single program.
Consider the scenario:
ProgramA ProgramB
SELECT Rowid FROM TableA
WHERE Column01 = "Value";-- engine returns rowid = 23
DELETE FROM TableA
WHERE Column01 = "Value"; -- deletes row including rowid 23
INSERT INTO TableA
VALUES ("Something-Else", ...); -- inserts new row with rowid 23
UPDATE TableA
SET Column01 = "New-Value"
WHERE Rowid = 23;
-- Oh dear; this isn't the row you thought it was because the SELECT was
-- not a SELECT FOR UPDATE. Tut, tut. You shouldn't use ROWID unless you
-- know it can't change behind the scenes!
Yours,
Jonathan Leffler (johnl@obelix.informix.com) #include <disclaimer.h>