Re: EXCHANGE PARTITION in Oracle8i

I suspect you mean INCLUDING INDEXES.

There is a note about this in my book.
In Oracle 8i, Oracle introduced some validation, despite the 'without validation' clause on the exchange. This checks primary and unique constraints. Run SQL_TRACE against the
session and you will see a horrendously
expensive piece of SQL executing as you
do the exchange.

To work around the issue, you need to
alter any PK or UK constraints on the
table and the exchange table to be
'enable novalidate'

--
Jonathan Lewis
http://www.jlcomp.demon.co.uk

Host to The Co-Operative Oracle Users' FAQ
http://www.jlcomp.demon.co.uk/faq/ind_faq.html

Author of:
Practical Oracle 8i: Building Efficient Databases

Screen saver or Life saver: http://www.ud.com
Use spare CPU to assist in cancer research.

René Wachs wrote in message <3bd32aec_4_at_news.bluewin.ch>...


>Hi

>

>We've to move data from a preload area (invisible for end users) to a

>productive area (visible to end users). We developed under Oracle8.0 and

>used the ALTER TABLE EXCHANGE PARTITION command (because we've partitioned

>tables). This worked very well. Now we've upgraded to Oracle8i and the same

>command lasts hours (instead a few seconds in Oracle8.0). Has somebody any

>advice why this effect happens ? We use the EXCHANGE PARTITION EXCLUDING

>INDEXES and WITHOUT VALIDATION (that's normally the fastest way).

>

>We can't use INSERT INTO command because our tables have more than

>20'000'000 rows and size between 3 and 5 GB a partition.

>

>Thanks for any reply.

>

>René

>

>

>