Home » RDBMS Server » Performance Tuning » enable row movement - ok, but why internally this thing is needed (11.2.0.3.0)
enable row movement - ok, but why internally this thing is needed [message #584795] Fri, 17 May 2013 13:50 Go to next message
piotrtal
Messages: 152
Registered: June 2011
Location: Poland/Czestochowa
Senior Member

Hi,

I was digging throught net to find answer for this question but I failed. I know when we should enable row movement on tables (3 cases);
- during updading partition key on partitioned tables
- shrinking tables
- flashbacking tables

and it is easy and clear instruction, but WHY? why internally oracle need to have this row movement enabled? normally during this three operations row can change its rowid but why we need enable this feature manually, and why we need to remember about that? why this option is not enabled as default for each table since it almost does not utilize database.
will someone from you be willing explain me why this option needs to be enabled manually?

by declaring desire of changing rowid for rows we are giving instruction for oracle that we agree with moving rows between blocks, but why we need declare this explicitly.

thanks.
Re: enable row movement - ok, but why internally this thing is needed [message #584800 is a reply to message #584795] Fri, 17 May 2013 14:10 Go to previous messageGo to next message
Michel Cadot
Messages: 59407
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Simply because before the rowid of a row was definitive and cannot changed during its life and some applications store this information to access more quickly to the row and so if the rowid of a row can change the application no more works.

Restraining to enable row movement means that you know what you do.

Regards
Michel

[Updated on: Fri, 17 May 2013 14:11]

Report message to a moderator

Re: enable row movement - ok, but why internally this thing is needed [message #584802 is a reply to message #584795] Fri, 17 May 2013 14:17 Go to previous messageGo to next message
John Watson
Messages: 4676
Registered: January 2010
Location: Global Village
Senior Member
I asked this question many years ago when SHRINK SPACE was first introduced, and was told something like "these are serious operations that may have major implications and the DBA should consider carefully before attempting them" which corresponds to Michel's answer. However, I think it is rubbish (the concept, not the answer). An ALTER TABLE MOVE will change all your rowids, and you don't need to enable row movement for that.
I suspect that there is something buried deep in the Oracle binaries that makes it necessary, and we normal human beings will never know what it is.
Re: enable row movement - ok, but why internally this thing is needed [message #584803 is a reply to message #584800] Fri, 17 May 2013 14:18 Go to previous messageGo to next message
piotrtal
Messages: 152
Registered: June 2011
Location: Poland/Czestochowa
Senior Member

hm... it make sense. thanks Michel. so assuming such operations should be performed only during maintenance window or during low database utilization period.
Re: enable row movement - ok, but why internally this thing is needed [message #584805 is a reply to message #584803] Fri, 17 May 2013 14:43 Go to previous message
Flyby
Messages: 146
Registered: March 2011
Location: Belgium
Senior Member
Found out some third party licenses were checking on rowid the hard way.
Previous Topic: commit_write in 10g
Next Topic: Need help in performance tunning for querying table
Goto Forum:
  


Current Time: Tue Oct 21 04:58:39 CDT 2014

Total time taken to generate the page: 0.05089 seconds