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 <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