Row Movement disabled in Partitioned table [message #671245] |
Sun, 19 August 2018 23:11 |
|
saipradyumn
Messages: 419 Registered: October 2011 Location: Hyderabad
|
Senior Member |
|
|
Hi All,
In Production environment ROW_MOVEMENT is disabled on one of the Partition table .
We trying to update the partition key column which causes partition change.
Table Name : CITY_OFFICES
Partition Type : RANGE
Interval :NUMTODSINTERVAL(1,'DAY')
Column Name : TIMESTAMP
Column Datatype : Date
ORA-14402: updating partition key column would cause a partition change
In order to resolve that we are going for enabling the ROW MOVEMENT
ALTER TABLE CITY_OFFICES ENABLE ROW MOVEMENT;
I know that it will resolve my actual issue.
But is there any other impact if I Enable the row movement for that table
?
As that is production environment wants to check before enabling that ?
Please help me
Thanks
Sharavathi
[Updated on: Mon, 20 August 2018 00:03] Report message to a moderator
|
|
|
|
|
|
Re: Row Movement disabled in Partitioned table [message #671258 is a reply to message #671245] |
Mon, 20 August 2018 06:19 |
John Watson
Messages: 8951 Registered: January 2010 Location: Global Village
|
Senior Member |
|
|
I am not clear on Oracle's logic behind row movement, though it is all well documented.
The idea is that rowids should not change. So if a row has to be moved to a different block consequent on UPDATE, it is not in fact moved: it is migrated. This means that the associated index entries do not need to be changed, though subsequent SELECT may degrade. So this behaviour favours UPDATE and SELECT pays the price. A side effect is that operations that require rowids to change such as your update of the partition key column will fail. Other such operations are SHRINK SPACE and FLASHBACK TABLE. Enabling row movement means that all these operations are permitted so that rows will move and rowids will change, but it does not have any effect on the row migration behaviour.
It has always seemed to me (I may be wrong) that enabling row movement cannot have any bad effect. However, I do sometimes think it would be nice if it actually changed the migrate-on-update mechanism to move-on-update.
|
|
|
Re: Row Movement disabled in Partitioned table [message #671259 is a reply to message #671258] |
Mon, 20 August 2018 06:30 |
Roachcoach
Messages: 1576 Registered: May 2010 Location: UK
|
Senior Member |
|
|
The rationale behind migrating is/was https://asktom.oracle.com/pls/apex/asktom.search?tag=row-migration
The Big Man it would seriously damage the index structure.
In an index, you have the rowid as part of the key. An update to a row might have to update 6 indexes (cause the row moved) and update the indexes -- MOVING the rows in the index, perhaps splitting that up - but definitely cause tons of extra work.
An occasional extra IO on an indexed read was deemed preferrable to having to reach out and touch tons of index data.
Additionally -- with the exception of:
o index organized tables when you update a primary key
o partitioned tables with "enable row movement" specifically turned on and an update to the partition key
a rowid is immutable -- it is assigned to a row upon insert and will never change. Many tools count on this fact (replication used to but still can, indexes do, Oracle Forms does by default for row locking, and so on)
At least the last I heard.
|
|
|