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 <[EMAIL PROTECTED]> Sent by: [EMAIL PROTECTED] 07/24/2002 01:40 PM Please respond to ORACLE-L To: Multiple recipients of list ORACLE-L <[EMAIL PROTECTED]> 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. [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). -- Please see the official ORACLE-L FAQ: http://www.
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. [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).
Re: reorg and rebuild
With CTAS, truncate and insert, indexes will be rebuilt automagically. I used to do index rebuilds (alter index rebuild) for superstitious reasons only. Occasionally, when the time needed to do a range scan increased dramatically, it has even benefited the performance. On 2002.07.24 15:03 Peter R wrote: > > Hi Friends, > > Iam reorganizing my tables thru copy(CTAS), So when Iam re-inserting > data into my Original table Will the indexes will build automatically > or I have to rebuild?? Here Iam not doing for indexes anything i.e > Iam not disabling it. > > I want to create table thru CTAS, truncate original table, copy back > data into original table from duplicate table. So that I can pack > data and get back my space back. My environment is v7.3 on AIX. > > > Thanks in advance > peter. > > > _ > Chat with friends online, try MSN Messenger: http://messenger.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). -- Mladen Gogala -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Mladen Gogala 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).
RE: reorg and rebuild
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).
Re: reorg and rebuild
Peter, Unless you drop the indexes and constraints they still exist in the original table. Truncating the table will reset the HWM on the tables and indexes. The reload will generally be faster if you drop the indexes before you load the data and then recreate the indexes. Be sure to check for invalid indices after you are finished. Ron ROR mª¿ªm >>> [EMAIL PROTECTED] 07/24/02 03:03PM >>> Hi Friends, Iam reorganizing my tables thru copy(CTAS), So when Iam re-inserting data into my Original table Will the indexes will build automatically or I have to rebuild?? Here Iam not doing for indexes anything i.e Iam not disabling it. I want to create table thru CTAS, truncate original table, copy back data into original table from duplicate table. So that I can pack data and get back my space back. My environment is v7.3 on AIX. Thanks in advance peter. _ Chat with friends online, try MSN Messenger: http://messenger.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: Ron Rogers 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).
RE: reorg and rebuild
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] -Original Message- Sent: Wednesday, July 24, 2002 2:04 PM To: Multiple recipients of list ORACLE-L Hi Friends, Iam reorganizing my tables thru copy(CTAS), So when Iam re-inserting data into my Original table Will the indexes will build automatically or I have to rebuild?? Here Iam not doing for indexes anything i.e Iam not disabling it. I want to create table thru CTAS, truncate original table, copy back data into original table from duplicate table. So that I can pack data and get back my space back. My environment is v7.3 on AIX. Thanks in advance peter. _ Chat with friends online, try MSN Messenger: http://messenger.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).
reorg and rebuild
Hi Friends, Iam reorganizing my tables thru copy(CTAS), So when Iam re-inserting data into my Original table Will the indexes will build automatically or I have to rebuild?? Here Iam not doing for indexes anything i.e Iam not disabling it. I want to create table thru CTAS, truncate original table, copy back data into original table from duplicate table. So that I can pack data and get back my space back. My environment is v7.3 on AIX. Thanks in advance peter. _ Chat with friends online, try MSN Messenger: http://messenger.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).
