Rowid reused

[Viswanathan Raman-w2889c]

Hi ,

If a row is deleted and after that if update statistics executed on that table will the rowid be rearranged.

Regards
V.RAMAN

[Andrew Hamm]

Viswanathan Raman-w2889c wrote:
>
> If a row is deleted and after that if update statistics executed on
> that table will the rowid be rearranged.
>

Update stats does not affect rowids.A row's rowid will be changed if:

1) the table is reloaded.
2) The table is (re-)clustered (ie re-arranged in physical order in
connection with a clustered index)
3) If a "fast inplace alter" of the table schema is made, and a row is
updated such that it needs rewriting, it will be written with it's new shape
to a new address
4) fragmentation commands are used to move the table spaces (even if the
table is not actually fragmented before or after the command is used to move
the table to a different dbspace)
5) Other reasons .... ;-)

Rules of rowids:

1) NEVER store them in another table. An exception would be in a temp table
during a particular process, as long as you can guarantee that none of the
above effects causes a change of rowid.
2) Never attach any meaning to the values of the rowid. This includes
thinking about the sequencing of the rows. There will probably be gaps in
the rowid sequence, and re-used slots will mean that a new row will not
necessarily have a higher rowid than a previously inserted row. This will
DEFINITELY be the case if you do any deletes (or point 3 kicks in)
3) NEVER STORE ROWID'S IN ANOTHER TABLE.
4) NEVER STORE ROWID'S IN ANOTHER TABLE.
5) NEVER STORE ROWID'S IN ANOTHER TABLE.

[Madison Pruet]

The rowid can be reused when the page has had more than 255 rows inserted on it.

Eventually the rowid will be reused, but it doesn't have anything to do with update statistics.

Viswanathan Raman-w2889c wrote:

--
---------------------------------------
Madison Pruet
Enterprise Replication Product Development
IBM Informix Dynamic Server
Dallas, Texas

[Jack Parker]

no. Update statistics does not change anything about the table, merely what
the catalogues know about the table.

cheers
j.

[Rajib Sarkar]

Update stats is not going to change the ROWID ... but if you delete a row
and the engine inserts a row in the same slot, the ROWID will be reused. In
Informix the ROWID is the physical representation of a row ..from the rowid
the engine finds out what LOGICAL PAGE (within the TABLESPACE) and at what
slot # the row is residing. The format is 0xLLLLLLSS (where L == Logical
Page, S = Slot#).

The only place I have seen a rowid being changed (without deletion) is
IN-PLACE ALTER. When you do an in-place alter, the table data is not
touched at all, only the PARTITION PAGES are updated to signify that an
ALTER TABLE has happened. Now, when you update a row in the table, the PAGE
where the ROW is residing is moved as a WHOLE to a new PAGE signifying the
newer version of the page. Since the format of a rowid consists of Logical
Page, the ROWID changes in this case (BTW, the index keys are also changed
to reflect the new rowid in this case).

That's why in Informix, it is not really recommended to use ROWID to select
rows from a table, as it can change and you might get wrong results.

HTH

Thanx much,

Rajib Sarkar
Advisory Support Engineer(Wells Fargo Bank)
IBM Software Group -- Data Management
Ph: 602-2172100, Fax: 602-2172100

www.ibm.com/software

"Jack Parker"
<jparker@artentec To: "Viswanathan Raman-w2889c" <w28...@motorola.com>, <inform...@iiug.org>
h.com> cc:
Sent by: Subject: Re: Rowid reused
owner-informix-li
s...@iiug.org


07/16/2002 06:28
AM

[Madison Pruet]

Also an alter index to cluster will change the rowid.

M.P.

Rajib Sarkar wrote:

--

[Postm...@127.0.0.1]

Forgive me for asking, but what does the author want with rowid?
Its not unique in a clustered environment. no?

Could it be a situation where they would want a serial column instead?

-Uncle Mikey