Home » RDBMS Server » Performance Tuning » Enable Row Movement in Partitioning and Overhead
Enable Row Movement in Partitioning and Overhead [message #187312] Fri, 11 August 2006 16:09 Go to next message
Messages: 294
Registered: February 2006
Senior Member

I am partitioning few huge tables,
regarding The feature of Enable Row movement i need some clarifications :

First Question

Does partitioning column should not be ever updated or what ? if normal updates with in that partition is it an extra overhead or have some performance impact..
Second doubt is :-
If update causes row movement from 1 partition to other will it have performance impact.

These question has been raised by one of my senior DBA since when i started partitioning on DATE column (range ) and the application was updating the date column and in some cases it falls out of partition and error came so , I enabled row movement it is resolved, but he says it's an extra over head..
Usually in our environment once at month end there are chances that this partitioning column might get updated and the new value might fall in new partition..
Do you people say that partitioning column should never be updated or what ?

Some experience tips on this please.


[Updated on: Fri, 11 August 2006 16:11]

Report message to a moderator

Re: Enable Row Movement in Partitioning and Overhead [message #187693 is a reply to message #187312] Mon, 14 August 2006 21:52 Go to previous message
Messages: 3724
Registered: October 2005
Location: Melbourne, Australia
Senior Member
I would say that 99% of partitioned tables would use a non-updateable column.

Factors to consider:
- Overhead of moving between partitions
- Overhead of Oracle checking to make sure that a row doesn't move partitions.
- Effect of empty space left in locally partitioned indexes
- Effect of empty space left in table partitions.

The effect will depend on how many partitions are in the table, the number of LOCALLY partitioned indexes, the number of rows your move, and the proportion of rows in a partition that move.

If you want to make your partition key updateable, then why don't you benchmark it using your table, and time the results:
- Update 100K rows - a column that is not the partition key
- Update 100K rows on the partition column, but not so that the rows will move partitions
- Update 100K rows on the partition column so that they move partition.

Ross Leishman
Previous Topic: How to tune a select query which gives a result of 120 million rows?
Next Topic: Need help on explain plan
Goto Forum:

Current Time: Tue Oct 25 14:22:05 CDT 2016

Total time taken to generate the page: 0.84862 seconds