| Enable Row Movement in Partitioning and Overhead [message #187312] |
Fri, 11 August 2006 16:09  |
faiz_hyd
Messages: 294
Registered: February 2006
|
Senior Member |
|
|
Hi,
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.
Thanks.
[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  |
rleishman
Messages: 3728
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
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
