RE: Partition exchange

Index maintenance makes sense but all the indexes are local so I am
confused.

 

 

Thanks,

Ken

  _____  

From: Darrell Landrum [mailto:darrell@xxxxxxxxxxx] 
Sent: Wednesday, March 12, 2008 11:54 PM
To: Ken Naim
Cc: oracle-l@xxxxxxxxxxxxx
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@xxxxxxxxxxxxx [mailto:oracle-l-bounce@xxxxxxxxxxxxx]
On Behalf Of Darrell Landrum
Sent: Wednesday, March 12, 2008 8:47 PM
Cc: oracle-l@xxxxxxxxxxxxx
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

Other related posts: