enabling ROW_MOVEMENT in Oracle 19?-CodePudding

I have table TRD_TEST2 in which I have created below partition for performance improvement as the table has 80 million records:

PARTITION BY RANGE(valid_to)
    INTERVAL(NUMTOYMINTERVAL(1, 'MONTH')) 
        ( PARTITION p0 VALUES LESS THAN (TO_DATE('01-01-3999', 'DD-MM-YYYY')))
         ENABLE ROW MOVEMENT;

For history purposes, we have created technical key columns valid_from and valid_to in TRD_TEST2 table. Below is the Merge query which we are running daily:

MERGE INTO TRD_TEST2 e
    USING TRD_TEST2_SRC h
    ON (e.ld= h.ld)
  WHEN MATCHED THEN
    UPDATE SET e.valid_to = sysdate,
    IS_CURRENT = 0
where e.valid_to = TO_DATE('01.01.3999', 'DD.MM.YYYY');

I would like to know does it make sense to enable ENABLE ROW MOVEMENT in this case as we are updating valid_to column using the above Merge query daily and what is the impact?

CodePudding user response：

I'm not sure that the question makes sense.

If you are partitioning on valid_to and updating valid_to then you must enable row movement. If you don't enable row movement, as soon as you try to update a row in a way that would force it into a different partition, you'll get an error.

Choosing a partition key that is expected to change is generally frowned upon. Moving a row effectively turns an update into a delete and insert which will be significantly more expensive (how much will depend on how much of the cost is redo generation and how much redo is generated by the two operations). As was suggested in your earlier question, partitioning on valid_from would seem to make more sense in general since it is a static, NOT NULL column. And that saves you from having to assign fake valid_to values to current rows. If your only concern is the performance of this merge statement rather than all the other queries against this table, though, partitioning on valid_to might make sense for you.