RE: Partition exchange

Yes I do but the question then becomes, why does it validate it when I say "without validation" but does not when I omit that clause?  

Thanks much for your help.  

Ken  

From: Ghassan Salem [mailto:salem.ghassan_at_gmail.com] Sent: Thursday, March 13, 2008 4:58 AM
To: kennaim_at_gmail.com
Cc: Darrell Landrum; oracle-l_at_freelists.org Subject: Re: Partition exchange  

I bet you have a PK (constraint, not only a unique index) on the table. When doing a partition exchange, Oracle will validate the PK even when you say 'without validation'.

rgds

On Thu, Mar 13, 2008 at 9:05 AM, Ken Naim <kennaim_at_gmail.com> wrote:

Seems the full scans are caused by the without validation clause which is counter intuitive. I have not been able to find anything on google, asktom or Metalink. But at least I can exchange partitions now without each one taking 3 hours * 18 of them.      

From: Darrell Landrum [mailto:darrell_at_landrum.com]

Sent: Wednesday, March 12, 2008 11:54 PM To: Ken Naim

Cc: oracle-l_at_freelists.org
Subject: Re: Partition exchange  

I could see the partition exchange from partitioned table in tablespace A to heap table in tablespace B taking a long time and I could guess that it would be possible for a full table scan involving all partitions to occur for index maintainance (are any of the indexes global?) or other reason that I'm not thinking of right now. I also agree with you that the partition exchange from heap table in tablespace B to partitioned table in tablespace B should be pretty quick, but there still may be a matter of index maintenance. I've done this exact type of operation for similar reasons, but dropped the indexes first and rebuilt afterward so I'm in part guessing until I can test as well.    

On Mar 12, 2008, at 10:20 PM, Ken Naim wrote:  

I could understand it if the full scan was on a partition but all 18 partitions are being scanned. Also the partition after the exchange is the right tablespace (same one that it was in when it was a heap table). So that doesn't make much sense to me.  

Ken  

From: oracle-l-bounce_at_freelists.org [mailto:oracle-l-bounce_at_freelists.org] On Behalf Of Darrell Landrum
Sent: Wednesday, March 12, 2008 8:47 PM
Cc: oracle-l_at_freelists.org
Subject: Re: Partition exchange  

Hey Ken,  

When doing the partition exchange to a different tablespace, all of the data has to be moved. Data dictionary updates "only" would only apply if keeping the segments in the same tablespace.  

Regards,

Darrell    

On Mar 12, 2008, at 7:35 PM, Ken Naim wrote:

I am in the process of moving the partitions of a table from ASSM to a non-assm tablespace using the partition exchange method (create non partitioned table, indexes, constraints etc.). When I do the alter table exchange partition including indexes without validation command it runs for hours, doing a full table scan on all the partitions of the partitioned table which takes many hours. I understood that just the data dictionary is updated, and it should take a few seconds. Primary Wait event is db file scattered read. Can anyone shed some light on this phenomenon. DB version is 10.2.0.3 and is running on Solaris 10.

Thanks,

Ken      

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