RE: Are rowids always in increasing order?

-----Original Message----- From: Jean Sagi [mailto:jeansagi@myrealbox.com] >I wonder if anyone one knows if IDS (9.3) asigns >rowids in increasing order? >I mean Always. NO. >In the documentation it is stated that: >"The database server assigns to each row in the >rowid column a unique number that remains stable >for the life of the row." Even this is not true. Several things can cause a row to get a new rowid: 1) reloading. Fairly obvious really.... 2) applying a cluster index. 3) "inplace alters" followed by an update of the row. The last one needs explaining. When you ALTER TABLE and do one of a set number of changes, eg adding or removing a column, or some "safe" data type changes of a column, the database engine does NOT rewrite the table. Instead, it keeps a record of each version of a row, and leaves the row in the old state. When you fetch a row, the data given to you is secretly transformed to the new shape. This only happens to the copy of the row that is fetched: the physical row in the table is not transformed just because you've selected it. However, when you update a row, the row needs to be rewritten, and the engine does not try to stuff the row back into the old slot. That would be impossible in the case of new or bigger columns, so the engine doesn't even try. When you update, the row is written in the new shape. This almost always causes the row to be written somewhere else, which means that an updated row may get a different ROWID. It used to be the case that you could safely count on a rowid being stable over the duration of a transaction, but even that is not true any more. One of the worst mistakes you can make is to store a rowid in another table and expect it to be useful to get the referred row. As for allocation of the rowids, they are not sequential because the rowid is based on a formula calculation that dpends on the page number and the offset within the page. They are not necessarily increasing because the page used to store a new row may be "further back" in the table space - ie a page with a lesser address than the last page used. Summary: never use rowids; NEVER store rowids; you are even taking a risk these days using them temporarily in 4GL code. --------------------------------------------------------------------- This email is from Civica Pty Limited and it, together with any attachments, is confidential to the intended recipient(s) and the contents may be legally privileged or contain proprietary and private information. It is intended solely for the person to whom it is addressed. If you are not an intended recipient, you may not review, copy or distribute this email. If received in error, please notify the sender and delete the message from your system immediately.Any views or opinions expressed in this email and any files transmitted with it are those of the author only and may not necessarily reflect the views of Civica and do not create any legally binding rights or obligations whatsoever. Unless otherwise pre-agreed by exchange of hard copy documents signed by duly authorised representatives, contracts may not be concluded on behalf of Civica by email. Please note that neither Civica nor the sender accepts any responsibility for any viruses and it is your responsibility to scan the email and the attachments (if any). All email received and sent by Civica may be monitored to protect the business interests of Civica. ---------------------------------------------------------------------