Here’s a question that appeared recently on OTN, and it’s one I’ve wondered about a few times – but never spent any time investigating. Are there any overheads to enabling row movement on a table ? If not, why is it not enabled by default and eliminated as an option ?
Obviously there are costs when a row moves – it will be updated, deleted and re-inserted with all relevant index entries adjusted accordingly – but is there an inherent overhead even if you do nothing to move a single row ?
Equally obviously you’ve made it possible for some to “accidentally” shrink the table, cause short term locking problems, and longer term performance probems; similarly it becomes possible to update rows in partitioned tables in a way that causes them to move; but “someone might do it wrong” doesn’t really work as an argument for “de-featurising” something that need not have been a feature in the first place.
What have I missed ?
Answers in the comments gratefully received – and possibly discussed.
Update (Oct 2016)
At the Trivadis Performance Days Roger MacNicol (of the Oracle.com blog Smart-Scan Deep Dive) made the comment that when you enable row movement there is no guarantee that Oracle won’t do something you are not expecting. The only thing the manual says is that if your application depends on rowids then you should not enable row movement. Consider, for example, that an update to a row in an Exadata compression unit currently flags the row and migrates it to another block – but leaves any indexes on the row pointing to the original rowid. There is no guarantee that Oracle won’t, one day, change the code to delete the whole row, re-insert it, and update the indexes if you’ve enabled row movement.
A carefree attitude to row movement in the current version of Oracle might lead to horrible surprises on the next upgrade.