rowid
Frank asked whether, in a non-fragmented IDS 11.50 table, a newly inserted row's rowid is always higher than earlier ones. The consensus answer: no guarantee. Rowid is just a page/slot address, space from deleted rows gets reused, and repack, cluster, in-place alters or table reorganisation can relocate rows; fragmented tables break it entirely. Advice was to never store rowids and to use a SERIAL/sequence column instead, with MAX() on an indexed serial normally cheaper. A later poster noted SE's rowid is a sequential row number, so MAX(rowid) works there if nothing is deleted, and claimed it benchmarked ~30% faster than MAX(serial).
Auto-generated by DrWatson from the posts below — may be imperfect; read the full thread.
HI, Guys, IDS11.50 FC8 For a non-fragmented table, can we guarantee that the rowid of a later inserted row is greater than the rowid of a previously inserted one? Thanks, Frank --bcaec54c546ef21f7404beaa8da5
On Fri, Apr 27, 2012 at 08:14, FRANK <yunyaoqu@gmail.com> wrote: > IDS11.50 FC8 > > For a non-fragmented table, can we guarantee that the rowid of a later > inserted row is greater than the rowid of a previously inserted one? > No, especially if you delete any rows. Generally, yes, but definitely not guaranteed. Also, do not store ROWID values. -- Jonathan Leffler <jonathan.leffler@gmail.com> #include <disclaimer.h> Guardian of DBD::Informix - v2011.0612 - http://dbi.perl.org "Blessed are we who can laugh at ourselves, for we shall never cease to be amused." --f46d0421ad61884e8304beaa9885
Hi, there is no such warrenty, better use a sequential column and insert with a null value, so it will be auto-assigned. Unless you assign the value on load, it is relatively valid to assume a new record will have a bigger ID. Marcus ----- Ursprüngliche Mail ----- Von: "FRANK" <yunyaoqu@gmail.com> An: ids@iiug.org Gesendet: Freitag, 27. April 2012 17:14:05 Betreff: rowid [26859] HI, Guys, IDS11.50 FC8 For a non-fragmented table, can we guarantee that the rowid of a later inserted row is greater than the rowid of a previously inserted one? Thanks, Frank --bcaec54c546ef21f7404beaa8da5 ******************************************************************************* Forum Note: Use "Reply" to post a response in the discussion forum.
Thanks Jonathan!! Frank On Fri, Apr 27, 2012 at 11:17 AM, Jonathan Leffler < jonathan.leffler@gmail.com> wrote: > On Fri, Apr 27, 2012 at 08:14, FRANK <yunyaoqu@gmail.com> wrote: > > > IDS11.50 FC8 > > > > For a non-fragmented table, can we guarantee that the rowid of a later > > inserted row is greater than the rowid of a previously inserted one? > > > > No, especially if you delete any rows. Generally, yes, but definitely not > guaranteed. > > Also, do not store ROWID values. > > -- > Jonathan Leffler <jonathan.leffler@gmail.com> #include <disclaimer.h> > Guardian of DBD::Informix - v2011.0612 - http://dbi.perl.org > "Blessed are we who can laugh at ourselves, for we shall never cease to be > amused." > > --f46d0421ad61884e8304beaa9885 > > > > ******************************************************************************* > Forum Note: Use "Reply" to post a response in the discussion forum. > > --bcaec554001a71db8a04beaad80e
No, this will not be guarantee. A very simple case which will violate this rule, would be a delete of the first row you inserted. A subsequent insert will reuse this space and get a rowid smaller than its previous. John F. Miller III STSM, Embedability Architect miller3@us.ibm.com 503-578-5645 IBM Informix Dynamic Server (IDS) ids-bounces@iiug.org wrote on 04/27/2012 08:14:05 AM: > From: "FRANK" <yunyaoqu@gmail.com> > To: ids@iiug.org > Date: 04/27/2012 08:15 AM > Subject: rowid [26859] > Sent by: ids-bounces@iiug.org > > HI, Guys, > > IDS11.50 FC8 > > For a non-fragmented table, can we guarantee that the rowid of a later > inserted row is greater than the rowid of a previously inserted one? > > Thanks, > Frank > > --bcaec54c546ef21f7404beaa8da5 > > > ******************************************************************************* > Forum Note: Use "Reply" to post a response in the discussion forum. >
Nope. A rowid is just the page and slot number where the row ends up. The rowid is not some generated number. In general, a new row will be placed in the next slot on the last page, but if that is not possible, then the space from a deleted row in any page will be used. You can not rely on the rowid to indicate anything except where the data happens to be located. Cheers, Dick Snoke ASL Solution Architect IBM - Channel Works (404) 487-1595 dsnoke@us.ibm.com From: "FRANK" <yunyaoqu@gmail.com> To: ids@iiug.org, Date: 04/27/2012 11:15 AM Subject: rowid [26859] Sent by: ids-bounces@iiug.org HI, Guys, IDS11.50 FC8 For a non-fragmented table, can we guarantee that the rowid of a later inserted row is greater than the rowid of a previously inserted one? Thanks, Frank --bcaec54c546ef21f7404beaa8da5 ******************************************************************************* Forum Note: Use "Reply" to post a response in the discussion forum.
Hi Frank, The Rowid is a 4 byte number composed of a page number (the first 3 bytes ) and a slot number (1 byte). It is unique within a tablespace and only stored in the index btree if there is an index; this allows the engine to locate the appropriate row when you go thru and index. Even though it does not change for the lifetime of the row ( until it gets deleted , then it can be reused by a new row) Do not rely on it for any sequence or any other logic. Some people use it in a select and then reuse it when updating or deleting, since it is fast, but I advise not to do it in case you change your table to a fragmented table. In this last case you can add rowids to the table but it defeats the purpose . Do not use it. Khaled Bentebal de mon portable Le 27 avr. 2012 à 11:14, "FRANK" <yunyaoqu@gmail.com> a écrit : > HI, Guys, > > IDS11.50 FC8 > > For a non-fragmented table, can we guarantee that the rowid of a later > inserted row is greater than the rowid of a previously inserted one? > > Thanks, > Frank > > --bcaec54c546ef21f7404beaa8da5 > > > ******************************************************************************* > Forum Note: Use "Reply" to post a response in the discussion forum. >
Please be aware that while everything below is true, the repack, alter table to cluster, and other operation can change where a row lives, thu= s changing the rowids of a row. John F. Miller III STSM, Embedability Architect miller3@us.ibm.com 503-578-5645 IBM Informix Dynamic Server (IDS) ids-bounces@iiug.org wrote on 04/27/2012 09:29:40 AM: > From: "Khaled BENTEBAL" <khaled.bentebal@consult-ix.fr> > To: ids@iiug.org > Date: 04/27/2012 09:31 AM > Subject: Re: rowid [26866] > Sent by: ids-bounces@iiug.org > > Hi Frank, > > The Rowid is a 4 byte number composed of a page number (the first 3 bytes ) > and a slot number (1 byte). > It is unique within a tablespace and only stored in the index btree i= f there > is an index; this allows the engine to locate the appropriate row whe= n you go > thru and index. > > Even though it does not change for the lifetime of the row ( until it= gets > deleted , then it can be reused by a new row) Do not rely on it for a= ny > sequence or any other logic. > > Some people use it in a select and then reuse it when updating or deleting, > since it is fast, but I advise not to do it in case you change your > table to a > fragmented table. In this last case you can add rowids to the table b= ut it > defeats the purpose . > > Do not use it. > > Khaled Bentebal de mon portable > > Le 27 avr. 2012 =E0 11:14, "FRANK" <yunyaoqu@gmail.com> a =E9crit : > > > HI, Guys, > > > > IDS11.50 FC8 > > > > For a non-fragmented table, can we guarantee that the rowid of a la= ter > > inserted row is greater than the rowid of a previously inserted one= ? > > > > Thanks, > > Frank > > > > --bcaec54c546ef21f7404beaa8da5 > > > > > > > ***********************************************************************= ******** > > Forum Note: Use "Reply" to post a response in the discussion forum.= > > > > > ***********************************************************************= ******** > Forum Note: Use "Reply" to post a response in the discussion forum.= >=
can rowid be reliably used if no rows ever get deleted or no alter index to cluster is ever executed?
The Rowid is reliable of course. What do you want to use it for? As I said earlier, if you ever change your table to a fragmented table, your rowid is unique per tablespace unless you use the clause "with Rowids". Khaled Bentebal de mon portable Le 1 mai 2012 à 08:42, "FRANK J. COMPUTER" <frank_in_pr@hotmail.com> a écrit : > can rowid be reliably used if no rows ever get deleted or no alter index to > cluster is ever executed? > > > ******************************************************************************* > Forum Note: Use "Reply" to post a response in the discussion forum. >
ROWID should ONLY be used within the context of a single transaction. Even then, if the table's contents are volatile, ie unlike your question has lots of deletes and inserts, there is a danger. You should ABSOLUTELY never store the rowid from one table in another as a kind of foreign key. FYI, other operations than deletes with inserts and clustering an index can change the location of data rows and so their rowid. Using any of the API functions that optimize the table's storage (compress, pack, reorganize, etc.) or any alter that cannot be performed in-place will also relocate data rows. Art Art S. Kagel Advanced DataTools (www.advancedatatools.com) Blog: http://informix-myview.blogspot.com/ Disclaimer: Please keep in mind that my own opinions are my own opinions and do not reflect on my employer, Advanced DataTools, the IIUG, nor any other organization with which I am associated either explicitly, implicitly, or by inference. Neither do those opinions reflect those of other individuals affiliated with any entity with which I am affiliated nor those of the entities themselves. On Tue, May 1, 2012 at 2:42 AM, FRANK J. COMPUTER <frank_in_pr@hotmail.com>wrote: > can rowid be reliably used if no rows ever get deleted or no alter index to > cluster is ever executed? > > > > ******************************************************************************* > Forum Note: Use "Reply" to post a response in the discussion forum. > > --14dae9340d594ca31804befa2313
On Mon, Apr 30, 2012 at 23:42, FRANK J. COMPUTER <frank_in_pr@hotmail.com>wrote: > can rowid be reliably used if no rows ever get deleted or no alter index to > cluster is ever executed? > If: * the table is not fragmented * you do not store the ROWID values * you only use ROWID values inside the program and your other conditions are accurate, then during one run of a program, you can use ROWID with moderate confidence (the main source of concern still being deletes, which are officially precluded by the preconditions). Do not store a ROWID in the database; that is going to bite you, sooner or later. Officially, using ROWID like this has been deprecated since about 1995. In practice, you can get use it. OTOH, using a primary key value (or combination of values) is almost as quick as access by ROWID. Sometimes, people add a serial column to a table that otherwise has a composite primary key in order to provide simple but quick access. -- Jonathan Leffler <jonathan.leffler@gmail.com> #include <disclaimer.h> Guardian of DBD::Informix - v2011.0612 - http://dbi.perl.org "Blessed are we who can laugh at ourselves, for we shall never cease to be amused." --f46d040172fff93b9d04befab878
Don't forget that if your table has in-place alters active, then an update of a row can move it. In this case, the row id can change during one run of a program or even during a single transaction.
Given that I have a table which never gets reorged, altered or any of its rows deleted. The only reason I was contemplating using rowid is to "SELECT MAX(rowid) FROM table" in order to locate the most recently inserted row in the table, versus having to do do "SELECT MAX(pk_id) FROM table" {pk_id being a SERIAL column}
.. plus, I'm using SE (no fragmented tables in SE) and I noticed that rowid in SE tables is a sequential SERIAL-like number, and not a page/slot number, which starts with 1 and incremenents as more rows are inserted.
Note that SELECT MAX(pk_id) ... will be faster than SELECT MAX(rowid) ... if you have an index (or primary key or unique constraint) on that column. The engine can find the max of an indexed serial column with a single IO (reading the index's root node) whereas it will take at least two and possibly many more IOs to find the max rowid! Art Art S. Kagel Advanced DataTools (www.advancedatatools.com) Blog: http://informix-myview.blogspot.com/ Disclaimer: Please keep in mind that my own opinions are my own opinions and do not reflect on my employer, Advanced DataTools, the IIUG, nor any other organization with which I am associated either explicitly, implicitly, or by inference. Neither do those opinions reflect those of other individuals affiliated with any entity with which I am affiliated nor those of the entities themselves. On Tue, May 1, 2012 at 9:27 PM, FRANK J. COMPUTER <frank_in_pr@hotmail.com>wrote: > Given that I have a table which never gets reorged, altered or any of its > rows > deleted. The only reason I was contemplating using rowid is to "SELECT > MAX(rowid) FROM table" in order to locate the most recently inserted row in > the table, versus having to do do "SELECT MAX(pk_id) FROM table" {pk_id > being > a SERIAL column} > > > > ******************************************************************************* > Forum Note: Use "Reply" to post a response in the discussion forum. > > --14dae9340e21150ab004bf03c0c0
That's different, ROWID in SE is a different physical concept. It is the sequential row number in the table. So, the max rowid in SE is the number of rows (unless there were deletes. Note that this will only give the last row inserted if there are no deletes, which is your given criterion, so, I guess it will work. Art Art S. Kagel Advanced DataTools (www.advancedatatools.com) Blog: http://informix-myview.blogspot.com/ Disclaimer: Please keep in mind that my own opinions are my own opinions and do not reflect on my employer, Advanced DataTools, the IIUG, nor any other organization with which I am associated either explicitly, implicitly, or by inference. Neither do those opinions reflect those of other individuals affiliated with any entity with which I am affiliated nor those of the entities themselves. On Tue, May 1, 2012 at 9:34 PM, FRANK J. COMPUTER <frank_in_pr@hotmail.com>wrote: > ... plus, I'm using SE (no fragmented tables in SE) and I noticed that > rowid > in SE tables is a sequential SERIAL-like number, and not a page/slot > number, > which starts with 1 and incremenents as more rows are inserted. > > > > ******************************************************************************* > Forum Note: Use "Reply" to post a response in the discussion forum. > > --14dae9340c016c9d3e04bf03caba
Fragmented tables also "break" rowids. Why not just use a serial or a sequence? On 1 May 2012, at 07:42, FRANK J. COMPUTER wrote: > can rowid be reliably used if no rows ever get deleted or no alter index to > cluster is ever executed? > > > ******************************************************************************* > Forum Note: Use "Reply" to post a response in the discussion forum. >
As I mentioned in my previous post, there's no fragmentation with the SE engine. The reason I'd like to use rowid vs. the serial column (primary key) to find the most recently inserted row is because it's faster. I've already benchmarked both methods and rowid is about 30% faster!