| 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
Dennis,
CTAS can indeed create a table in parallel. You just need to keep in mind how the parallel insert works. i.e. a new extent is allocated for each server process to insert into.
Jared
DENNIS WILLIAMS <DWILLIAMS_at_LIFETOUCH.COM>
Sent by: root_at_fatcity.com
07/24/2002 01:40 PM
Please respond to ORACLE-L
To: Multiple recipients of list ORACLE-L <ORACLE-L_at_fatcity.com>
cc:
Subject: 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). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: INET: Jared.Still_at_radisys.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 - 19:13:20 CDT
 |
 |