Re: Help on rowid (SE 5.0) - Reminder

In <4f5fj7$17v@cssun.mathcs.emory.edu> Steve@weet.demon.co.uk (Steve Weet) writes: > >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. > And Informix is going away from the ROWID use. Informix has stated many times not to create your code based upon rowids.