Enable Row Movement in Partitioning and Overhead

Question 1:

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.


Question 2:

Hello,

Flashback table requires that enable row movement is activated for the
table. By default, this is disabled. Are there any disadvantages by enabling
this for all tables (except that the ROWIDs might change) ? Couldn't find
much about this in the docs or with Google.

Oracle 10.2.0.3.

Matthias


Answer 1:

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.



Answer 2:

The only disadvantage is logical. ROWID used to be fixed value and I've
seen some examples where ROWID has been used as a key to retrieve data,
much like it's used in the EXCEPTIONS table. With row movement enabled,
that stops making sense. Another situation in which row movement comes
into play is partitioning. Row movement must be enabled if you need rows
to move between partitions.

Answer 3:

To my knowledge for all practical purposes there is no performance
impact to enabling row movement. This is not to say that an activity
that actually caused mass movement of rows will not have a noticable
impact on your system but generally speaking this is probably not an
issue.

HTH -- Mark D Powell --