Peter
I had to do this task recently on our ERP system, so I will try to
recall what was important.
With 25-gig of data, you will increase your speed significantly by
dropping the indexes before you load the data back into the original table,
then rebuild the indexes when you are finished. As Mladen points out,
truncate doesn't drop the indexes or invalidate them, but after the
truncate, the indexes are still there and empty, and as you add each row,
Oracle will insert an entry into the appropriate point in each index.
Loading without the indexes and then creating them afterward is much more
efficient.
While you can create the temporary table by CTAS and avoid redo records,
you won't get that advantage when you are moving the records back.
If you can manage it, you should consider creating the second table with
a temporary name. For example if the original table is named MYTABLE, create
MYTABLE2, make MYTABLE read-only, copy all the records, then RENAME MYTABLE
TO MYTABLE_ORIG, then RENAME MYTABLE2 TO MYTABLE. Then do a backup before
you drop the original table. It is quick and easy to move the datafiles to
the drive you want later.
Unless I'm mistaken, CTAS will only use a single CPU, unless you've got
some other things going on, like parallel server or partitioned tables. If
I'm wrong on this, perhaps someone more knowledgeable will correct me. If
your server has multiple CPUs, I find that I get better performance by
creating PL/SQL scripts that move separate portions of the table
simultaneously, committing every few records. But this means you generate
redo records, so you will want to try to turn the archiving off while you
are performing this task. That is what I ended up doing for my rebuild that
I mentioned.
And of course you will be testing your procedures on your test system
before you attempt this on your production system.
Hope you get some ideas from this.
Dennis Williams
DBA
Lifetouch, Inc.
[EMAIL PROTECTED]
-----Original Message-----
Sent: Wednesday, July 24, 2002 3:04 PM
To: Multiple recipients of list ORACLE-L
Dennis,
This is ERP application, So I don't want to touch any structures!!But I want
my space back from last 3 heavy purges and want to improve response time by
repacking the data. By truncating my highwatermark will come down from 25Gb
size to half!! and it had 3 indexes size 15G.
>Date: Wed, 24 Jul 2002 11:29:45 -0800
>
>Peter
> If you don't do anything to the indexes, like drop them, they will
>be there when you load the data back. Of course, if your table is really
>large, then the load time will be longer.
> You may also want to investigate ALTER TABLE MOVE. Now that command
>will require you to rebuild the indexes.
> What are you trying to accomplish by moving, truncating, moving the
>data back?
>
>Dennis Williams
>DBA
>Lifetouch, Inc.
>[EMAIL PROTECTED]
>
>
_________________________________________________________________
Send and receive Hotmail on your mobile device: http://mobile.msn.com
--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author: Peter R
INET: [EMAIL PROTECTED]
Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051
San Diego, California -- Public Internet access / Mailing Lists
--------------------------------------------------------------------
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).
--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author: DENNIS WILLIAMS
INET: [EMAIL PROTECTED]
Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051
San Diego, California -- Public Internet access / Mailing Lists
--------------------------------------------------------------------
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).
