how to update a oracle partition




Here is the way about how to update a partition in an oracle table

 

 

alter table coll.allets enable row movement;

UPDATE coll.allets

SET snap_date = to_date(’29/03/2016′,’dd/mm/yyyy’)

WHERE snap_date = to_date(’25/03/2016′,’dd/mm/yyyy’);

commit;

alter table coll.allets disable row movement;

 

 

 

Updating a partition key is by default not possible

Enable row movement

 

It is then just like an insert and delete..

 

Followup December 10, 2008 – 4:26 pm UTC

…I think I read in one of your book that row movement is bad design….

What I wrote was:

You need to understand that, internally, row movement is done as if you had in fact deleted the row and reinserted it. It will update every single index on this table, and delete the old entry and insert a new one. It will do the physical work of a DELETE plus an INSERT. However, it is considered an update by Oracle even though it physically deletes and inserts the row¿therefore, it won¿t cause INSERT and DELETE triggers to fire, just the UPDATE triggers. Additionally, child tables that might prevent a DELETE due to a foreign key constraint won¿t. You do have to be prepared, however, for the extra work that will be performed; it is much more expensive than a normal UPDATE. Therefore, it would be a bad design decision to construct a system whereby the partition key was modified frequently and that modification would cause a partition movement.
……

Now, you modify the partition key once I presume – that is from “N” to “Y”

So, you do not modify the key of a row frequently.

However, if your goal is to find “N” rows “fast”, you might consider naming the column

I_am_not_null_if_not_processed

and populating that with ‘Y’ (or ‘N’, whatever) and setting it to NULL when it is processed.

Then index it.

the index will be tiny (as only the not null keys are indexed…) and the optimizer will use it to find the first non-processed row…

From previous review

December 30, 2008 – 10:23 am UTC

Reviewer: A reader

 

Author: admin