What does EXCHANGE PARTITION WITH TABLE really do underground ?

Hi,
We have a 8i DB containing a few ridiculously *huge* tables
("ridiculously"
because they never were partitioned) that we plan to convert into
partitioned
ones.
To me the easiest -and quickest ?- way of proceeding would be to create

an empty table with 11 partitions and to exchange our big tables data
with
them. But everything's got to stay online, everything must happen
during the
usual hours of activity of the applications, and we have for example a
table
with 180 million records that's supposed to be splitted into a more or
less
11-partition table.
Is this way of proceeding going to allow us not to impact the
applications too
much ? (it's a table incurring lots of DML statements all the time, 24
hours
per day)
Does a real physical move of data happen here ? If yes I'm afraid we
can't
afford this conversion... And of course we're dealing with an 8i DB.

Thanks, and regards.

Here "to me the easiest -and quickest ?- way of proceeding would
be to create an empty table with 11 partitions" read "would be to
create empty tables with 11..." instead (I of course do not intend
to merge all the tables into a single partitioned one !).

IIRC, the data doesn't move, but you will likely have issues with
constraints or rebuilding indices. They probably have to be locked to
rebuild, and the rebuild probably would get locked out by the dml
locks. It's been too long since I've done this sort of thing to
remember all the details, but Lewis' Practical Oracle8i book explains
what to watch for and test. Even 10g has issues with indices to watch
out for.

I think you are going to have to schedule downtime. You might consider
upgrading first.

jg
--
@home.com is bogus.
http://www.wired.com/news/culture/0,59297-0.html

Thank you Joel. I should've checked Metalink first, as I've found
several notes clearly specifying there's no data move...

"Spendius" <spendius@muchomail.com> wrote in message
news:1139839158.692067.9640@g14g2000cwa.googlegrou ps.com...
> Hi,
> We have a 8i DB containing a few ridiculously *huge* tables
> ("ridiculously"
> because they never were partitioned) that we plan to convert into
> partitioned
> ones.
> To me the easiest -and quickest ?- way of proceeding would be to create
>
> an empty table with 11 partitions and to exchange our big tables data
> with
> them. But everything's got to stay online, everything must happen
> during the
> usual hours of activity of the applications, and we have for example a
> table
> with 180 million records that's supposed to be splitted into a more or
> less
> 11-partition table.
> Is this way of proceeding going to allow us not to impact the
> applications too
> much ? (it's a table incurring lots of DML statements all the time, 24
> hours
> per day)
> Does a real physical move of data happen here ? If yes I'm afraid we
> can't
> afford this conversion... And of course we're dealing with an 8i DB.
>
> Thanks, and regards.
>

You can't do this in 8i.

You might consider upgrading to 9i, which
introduced online redefinition for exactly this
type of one-off operation. Even then, the
overhead will be massive.

During an exchange, Oracle expects to swap
the name of an ordinary table with the name of
a single partition in the data dictionary. (And tidy
up the names of related indexes etc.) The ordinary
table ought to contain exactly and only data that
belongs in the target partition - Oracle is NOT moving
data around.

Technically, you could replace a massive table with a
view which is a union all of the massive table and its
partitioned clone. Then write instead-of triggers that
ensure that all DML is applied to the correct table;
then write a batch move program that locked and
moved a couple of thousand rows at a time from the
massive table to the partitioned table. But it's NOT a
sensible idea, and you would probably find some of
your SQL that failed to optimize properly and brought
the system crashing to a standstill.


--
Regards

Jonathan Lewis
http://www.oracle.com/technology/com...ce1.html#lewis

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

Cost Based Oracle: Fundamentals
http://www.jlcomp.demon.co.uk/cbo_book/ind_book.html