RE: Partitioning question
- From: "David Kurtz" <info2@xxxxxxxxxxxxxxx>
- To: <roger_xu@xxxxxxxxxxx>, <davewendelken@xxxxxxxxxxxxx>, "Oracle-L" <oracle-l@xxxxxxxxxxxxx>
- Date: Wed, 12 Oct 2005 21:40:25 +0100
If the value of a partition column changes such that the row should be in a
different partition, you will get an error because ROW MOVEMENT is not
enabled by default. If you enable ROW MOVEMENT then it works. Here's a
little test
SCOTT.7:2580.GOFASTER.SYSTEM.GO-FASTER-3>CREATE TABLE dmk
2 (a NUMBER)
3 PARTITION BY LIST(a)
4 (PARTITION dmk_1 VALUES (1)
5 ,PARTITION dmk_2 VALUES (2)
6 );
Table created.
SCOTT.7:2580.GOFASTER.SYSTEM.GO-FASTER-3>INSERT INTO dmk VALUES (1);
1 row created.
SCOTT.7:2580.GOFASTER.SYSTEM.GO-FASTER-3>UPDATE dmk
2 SET a = 2
3 WHERE a = 1;
UPDATE dmk
*
ERROR at line 1:
ORA-14402: updating partition key column would cause a partition change
--The update fails because ROW MOVEMENT is not enabled.
SCOTT.7:2580.GOFASTER.SYSTEM.GO-FASTER-3>ALTER TABLE dmk ENABLE ROW
MOVEMENT;
Table altered.
SCOTT.7:2580.GOFASTER.SYSTEM.GO-FASTER-3>UPDATE dmk
2 SET a = 2
3 WHERE a = 1;
1 row updated.
--Now the update works because ROW MOVEMENT is enabled.
I have used partitioning in transactional systems. I often enable ROW
MOVEMENT to prevent application errors caused by updating partition key
values. Obviously, there is an additional overhead to row movement, because
the whole row has to be deleted and reinserted. You would have to decide
whether the advantages of a particular partitioning strategy outweighed the
overhead of any row movement.
For example, in PeopleSoft Global Payroll, I range partition the payroll
result tables by employee ID (trust me it makes sense, and the explanation
is long and not relavent to this discussion). However, in HR there a
facility to update an employees ID. It is intended for error correction.
It will update every table in the system with the column EMPLID. That could
cause rows to move from one partition to another. Generally there is no row
movement, but I have to enable the feature otherwise I will break a piece of
the application.
regards
_________________________
David Kurtz
Go-Faster Consultancy Ltd.
tel: +44 (0)7771 760660
fax: +44 (0)7092 348865
web: www.go-faster.co.uk
mailto:david.kurtz@xxxxxxxxxxxxxxx
Book: PeopleSoft for the Oracle DBA: http://www.psftdba.com
Next Go-Faster Seminar: PeopleSoft for the DBA, London UK, October
PeopleSoft DBA Forum: http://groups.yahoo.com/group/psftdba
> -----Original Message-----
> From: oracle-l-bounce@xxxxxxxxxxxxx
> [mailto:oracle-l-bounce@xxxxxxxxxxxxx]On Behalf Of Roger Xu
> Sent: 12 October 2005 20:49
> To: davewendelken@xxxxxxxxxxxxx; Oracle-L
> Subject: RE: Partitioning question
>
>
> What happens if a partition field changes?
> Will that record move from one partition to the other?
> Thanks.
>
> -----Original Message-----
> From: oracle-l-bounce@xxxxxxxxxxxxx
> [mailto:oracle-l-bounce@xxxxxxxxxxxxx] 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.
>
>
> --
> //www.freelists.org/webpage/oracle-l
>
> For technical support please email tech_support@xxxxxxxxxxx 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@xxxxxxxxxxxx
> --
> //www.freelists.org/webpage/oracle-l
>
--
//www.freelists.org/webpage/oracle-l
Other related posts:
