Re: Help on rowid (SE 5.0) - Reminder

javeed anwar <@psi.ernet.in> writes: > > > Dear Informix Friends, > > The problem is like this: > > I have got a table with large number of records (say 2 lakhs), which was > being inserted into the table over a period of time. After the creation of > table, only insert operation was performed on the table (ie: update and delete > operations are not done). This will result in the records having rowid's from > 1 to 200000. So far so good. What is a lakh by the way ? > Now, I delete 1 lakh records 8< SNIP SNIP >8 > > 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. I do not understand what you are trying to achieve here. The rowid's for the deleted records will now no longer exist so any attempt to fetch based on rowid will result in SQLCA.SQLCODE beings set to 100 (not found) The absence of particular rowids is not a performance problem in itself, however in SE mass deletes from a table will not automatically result in that data space becoming available for use i.e your table.dat file will not shrink. If this is part of what you wish to overcome then the only way to recover that data space is to rebuild the table from scratch. Informix provides a command "ALTER INDEX ... TO CLUSTER" a side effect of this command is that will rebuild the table for you and recover your dead space. I do not know how the rowid is stored, if at all, perhaps someone in the know could advise whether rowid's are stored or whether they are just offsets to rows. I hope that these questions have not arisen because you are using rowids as foreign keys. This is most definitely to be avoided. The alter index to cluster mentioned above will re-allocate all the rowids. Informix do NOT guarantee the stability of a rowid. -- ----------------------------------------------------------------------------- EnsignData Ltd. - Informix and Tetra Accounting systems consultancy +44 1634 577054 Steve Weet steve@weet.demon.co.uk