From oracle-l-bounce@freelists.org Wed Oct 12 15:45:08 2005 Return-Path: Received: from air891.startdedicated.com (root@localhost) by orafaq.com (8.12.10/8.12.10) with ESMTP id j9CKj8u9015123 for ; Wed, 12 Oct 2005 15:45:08 -0500 X-ClientAddr: 206.53.239.180 Received: from turing.freelists.org (freelists-180.iquest.net [206.53.239.180]) by air891.startdedicated.com (8.12.10/8.12.10) with ESMTP id j9CKixvX015095 for ; Wed, 12 Oct 2005 15:45:00 -0500 Received: from localhost (localhost [127.0.0.1]) by turing.freelists.org (Avenir Technologies Mail Multiplex) with ESMTP id AB2E71FD5A3; Wed, 12 Oct 2005 15:42:39 -0500 (EST) Received: from turing.freelists.org ([127.0.0.1]) by localhost (turing [127.0.0.1]) (amavisd-new, port 10024) with ESMTP id 20812-10; Wed, 12 Oct 2005 15:42:39 -0500 (EST) Received: from turing (localhost [127.0.0.1]) by turing.freelists.org (Avenir Technologies Mail Multiplex) with ESMTP id 25A051FD4FC; Wed, 12 Oct 2005 15:42:39 -0500 (EST) From: "David Kurtz" To: , , "Oracle-L" Subject: RE: Partitioning question Date: Wed, 12 Oct 2005 21:40:25 +0100 Message-ID: MIME-Version: 1.0 Content-Type: text/plain; charset="US-ASCII" In-Reply-To: X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2900.2670 X-archive-position: 26847 X-ecartis-version: Ecartis v1.0.0 Sender: oracle-l-bounce@freelists.org Errors-To: oracle-l-bounce@freelists.org X-original-sender: info2@go-faster.co.uk Precedence: normal Reply-To: info2@go-faster.co.uk X-list: oracle-l X-Virus-Scanned: by amavisd-new-20030616-p9 (Debian) at avenirtech.net X-mailscan-MailScanner-Information: Please contact the ISP for more information X-mailscan-MailScanner: Found to be clean X-MailScanner-From: oracle-l-bounce@freelists.org X-Spam-Checker-Version: SpamAssassin 2.63 (2004-01-11) on air891.startdedicated.com X-Spam-Level: X-Spam-Status: No, hits=-4.9 required=5.0 tests=AWL,BAYES_00 autolearn=ham version=2.63 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@go-faster.co.uk 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@freelists.org > [mailto:oracle-l-bounce@freelists.org]On Behalf Of Roger Xu > Sent: 12 October 2005 20:49 > To: davewendelken@earthlink.net; 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@freelists.org > [mailto:oracle-l-bounce@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@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@dp7uptx.com. > -- > http://www.freelists.org/webpage/oracle-l > -- http://www.freelists.org/webpage/oracle-l