| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Mailing Lists -> Oracle-L -> RE: reorg and rebuild
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.
dwilliams_at_lifetouch.com
-----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.
>dwilliams_at_lifetouch.com
>
>
-- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Peter R INET: niagarap_at_hotmail.com 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: ListGuru_at_fatcity.com (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: DWILLIAMS_at_LIFETOUCH.COM 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: ListGuru_at_fatcity.com (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 Wed Jul 24 2002 - 15:40:37 CDT
 |
 |