OraFAQ Forum: Performance Tuning » enable row movement - ok, but why internally this thing is needed

Home » RDBMS Server » Performance Tuning » enable row movement - ok, but why internally this thing is needed (11.2.0.3.0)

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

enable row movement - ok, but why internally this thing is needed [message #584795]

Fri, 17 May 2013 13:50

piotrtal
Messages: 168
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.

Report message to a moderator

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

Michel Cadot
Messages: 67649
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

John Watson
Messages: 8458
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.

Report message to a moderator

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

piotrtal
Messages: 168
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.

Report message to a moderator

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

Flyby
Messages: 188
Registered: March 2011
Location: Belgium

Senior Member

Found out some third party licenses were checking on rowid the hard way.

Report message to a moderator

Previous Topic:	commit_write in 10g
Next Topic:	Need help in performance tunning for querying table

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Sat Jan 23 05:38:22 CST 2021