Re: Partitioning question

You can alter the table to allow the rows can move/migrate.

alter table XX enable row movement.

then, if you update table set mypartitionkey=newvalue where someotherkey=X the field will magically move to the new partition.

Caveat: row migration has a potentially large penalty, especially on transactional systems (hence the advice of Mr. Wedelken below).

You can "force" data into the wrong partition at the onset by using alter table exchange partition without validation ... but once you do this, Oracle cannot "find" that data to migrate it at some point in the future, even if you directly update the partition key. Its very weird. You can force the migration by finding the record by another key ... but never again by the partitionkey itself. You won't be able delete the record or even query by it. I guess "without validation" really means it :-)

Todd

>
> What happens if a partition field changes?
> Will that record move from one partition to the other?
> Thanks.
>
> -----Original Message-----
> From: oracle-l-bounce_at_freelists.org
> [mailto:oracle-l-bounce_at_freelists.org] On Behalf Of david wendelken
> Sent: Wednesday, October 12, 2005 10:33 AM
> To: Oracle-L
> Subject: Re: Partitioning question
>
>
> Partitioning can also be used in transactional systems.
>
> And if you do, make sure it's on a field that has values that never
> change.
>
>
> --
> http://www.freelists.org/webpage/oracle-l
>
> For technical support please email tech_support_at_dp7uptx.com or you can
> call (972)721-8257.
> This email has been scanned for all viruses by the MessageLabs Email
> Security System.
>
>
>
> This e-mail is intended solely for the person or entity to which it is addressed and may contain confidential and/or privileged information. Any review, dissemination, copying, printing or other use of this e-mail by persons or entities other than the addressee is prohibited. If you have received this e-mail in error, please contact the sender immediately and delete the material.
> ____________________________________________________________________
> This email has been scanned for all viruses by the MessageLabs Email Security System. Any questions please call 972-721-8257 or email your request to tech_support_at_dp7uptx.com.
> --
> http://www.freelists.org/webpage/oracle-l
>

--
http://www.freelists.org/webpage/oracle-l