Re: Help on rowid (SE 5.0) - Reminder

Errmmm... >From: tonytd@ttyrwhit.demon.co.uk (Tony Tyrwhitt-Drake) >Date: Mon, 05 Feb 1996 23:52:52 GMT >X-Informix-List-Id: <news.20939> > >In article <4f54f0$hic@cssun.mathcs.emory.edu>, javi@psi.ernet.in (javeed anwar t) says: > >>What I want to know is : >> >>Suppose I do a partial scan or total scan on this table before and after >>deletion, how will be the performance of the system? I want to know because >>I doubt since the rowid's are not physically deleted it may degrade the >>performance in the second case. >> >>Also, let me know how the rowid behaves when that particular record is >>deleted from the table and how does an indexed scan filter the records? > >I get the feeling that you may have mis-understood the role of > rowids . > >A rowid is unique for the life of a table. If you drop the table >and reload it a particular row of data may have a new rowid. Otherwise >rowids start counting up from 1 and are never reused. Not so. Not so at all! ROWIDs correspond to a physical slot in the data storage area, in both OnLine and SE. In SE, it corresponds to the record number in the .dat file; in OnLine, it corresponds to a page and slot within page. While a particular row of data continues to exist, the ROWID does not change. Once a row is deleted, the physical slot is available for re-use, and some other row can be given the same ROWID. This is why you should NEVER store a ROWID in a permanent table -- what was valid as a cross-reference at time A may still be valid at time B, but might point to a completely different row. Note that ALTER TABLE and ALTER INDEX can revise the ROWIDs completely. >The performance of a table may depend on how many rows are in it, >but not what the values of the rowids are. If the table is well >designed and indexed then performance may not even be dependant on >number of rows in the table. The performance of a sequential scan will always depend, in part, on the amount of data (number of rows) in a table. Most indexed accesses will not depend very on the number of rows. It is more a function of the depth of the B-trees in the index, which is not linear with table size. Depth = O(log(N)), or thereabouts. >A rowid is just a unique key given to each row. I think you are getting confused between SERIAL and ROWID. Within fairly broad limits, once a serial value is allocated, it is not reused while the table exists. This is not absolutely true, but you can look in the FAQ Appendices for details about serial number rollover. You can also reuse serial values, but this is seldom done in practice. A serial value does not change, even if the table is reorganized. A ROWID is unique at any given time, but the ROWID is not guaranteed not to change over the life of a table, and especially not across a table reorganization. It is guaranteed not to change while a row continues to exist. Yours, Jonathan Leffler (johnl@informix.com) #include <disclaimer.h>