Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.server -> Re: What does EXCHANGE PARTITION WITH TABLE really do underground ?

Re: What does EXCHANGE PARTITION WITH TABLE really do underground ?

From: Jonathan Lewis <jonathan_at_jlcomp.demon.co.uk>
Date: Fri, 17 Feb 2006 11:24:11 +0000 (UTC)
Message-ID: <dt4bor$g5o$1@nwrdmz02.dmz.ncs.ea.ibs-infra.bt.com>


"Spendius" <spendius_at_muchomail.com> wrote in message news:1139839158.692067.9640_at_g14g2000cwa.googlegroups.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/community/oracle_ace/ace1.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
Received on Fri Feb 17 2006 - 05:24:11 CST

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US