RE: exchange partition in 9.2

From: Gurelei <gurelei_at_yahoo.com>
Date: Tue, 17 Jun 2003 07:29:43 -0700
Message-ID: <F001.005B2E7F.20030617065956@fatcity.com>

Arup and Jacques:

thanks for your help.

The command we are executing is alter table exchange partition ... with table .. include indexes. We have also a PK on each of the tables (local index on a partitioned table). Go global indices. We have tried the same command with novalidate and the results were the same.

Any thougths?
--- Jacques Kilchoer <[EMAIL PROTECTED]> wrote:
> Did you say alter table exchange partition ...
> validate or alter table exchange partition ...
> novalidate? If you say novalidate then Oracle will
> sort the exchange table to make sure that you are
> not creating duplicate rows for the PK constraint.
> There is a Metalink note on that.
> Does the table have a PK enforced by a locally
> partitioned unique index? Any global indexes?
> What options did you have on the exchange partition
> statement?
>
> > -----Original Message-----
> > From: Gurelei [mailto:[EMAIL PROTECTED]
> >
> > We are having an interesting issue with the
> exchange
> > partition command in our shop. We are running
> oracle
> > 9202 on AIX 5l. The exchange partition command
> used
> > to take about 1 sec (or less) in our old
> environmen
> > (oracle 81 Dynix 4.3.3). Now the same operation
> takes
> > about a minute and creates a ton of IO. an Oracle
> rep
> > said that this has to do something with the way
> > exchange partition now treats primary and unique
> keys.
> > Recently however a DBA on my team did 4 exchanges
> in a
> > row between the same two tables. First one took
> about
> > 45s and did a lot of IO. next one took almost no
> time
> > and no IO (and it did move the data), the next one
> -
> > again 45s and IOs, the fourth one - no time again.
> > Does any one has any experience with this issue
> and
> > may be can shed some light on it.
> --
> Please see the official ORACLE-L FAQ:
> http://www.orafaq.net
> --
> Author: Jacques Kilchoer
> INET: [EMAIL PROTECTED]
>
> Fat City Network Services -- 858-538-5051
> http://www.fatcity.com
> San Diego, California -- Mailing list and web
> hosting services

>

---

> To REMOVE yourself from this mailing list, send an
> E-Mail message
> to: [EMAIL PROTECTED] (note EXACT spelling of
> 'ListGuru') and in
> the message BODY, include a line containing: UNSUB
> ORACLE-L
> (or the name of mailing list you want to be removed
> from). You may
> also send the HELP command for other information
> (like subscribing).

---

Do you Yahoo!?
SBC Yahoo! DSL - Now only $29.95 per month! http://sbc.yahoo.com

-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: Gurelei
  INET: [EMAIL PROTECTED]

Fat City Network Services    -- 858-538-5051 http://www.fatcity.com
San Diego, California        -- Mailing list and web hosting services
---------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).

Received on Tue Jun 17 2003 - 09:29:43 CDT