Thanks to all who replied. Fortunately, the tables are relatively small, so the rebuild of the indicies shouldn't take very long.
-- Alan Davey [EMAIL PROTECTED] 212-604-0200 x106 On 7/9/02, Jack Silvey <[EMAIL PROTECTED]> wrote: >Alan, > >The alter table move command will invalidate all >existing indicies and you will have to rebuild them. >This is because the alter table move is implemented as >a CTAS in the background, and all of the rowids will >change. > >table move tips: > >1) use parallelism - however, parallel processes will >write to their own segments, and will trim the unused >space off the end for all but one of the segments >during the final merge of all the segments into the >new index segment. This can give you odd sized >segments, throwing off uniform space allocation (if >you use that). > > >index rebuilding tips: > >1) If your index is partitioned, you can rebuild the >partitions at the same time, and rebuild all of your >indexes at the same time. However, you cannot >update/insert/delete the table while this is going >forward, unless you use the "online" option, which has >limitations. >2) Use parallel (degree x) to rebuild, since it will >spawn off more processes and take less time. see space >considerations above. >3) Use nologging, unless you want to store your >indexes in the redo logs (otherwise, you can always >just recreate, much easier) >4) use the "compute statstics" clause to gather stats >during the build >5) consider using initrans 4 or better, otherwise, you >risk running into deadlocks during parallel updates >(doesn't take up that much more space - 23 bytes or >so). Same for pctfree - leave 1 pct, otherwise the ITL >list can't grow and you might get into trouble. >6) some indexes can be built online, which allows >updates to go forward during the rebuild. > > >hth, > >jack > > > > >--- Alan Davey <[EMAIL PROTECTED]> wrote: >> Hi All, >> >> I want to use the alter table move command (under >> 9i) to change the storage parameters for a couple of >> tables. The tables will remain in the current >> tablespace. Will I need to rebuild any indicies on >> that table, or will the rowid's be updated >> automatically. >> >> I've looked in the FM, but I don't see any caveats >> about indicies when using the move option. So, am I >> safe in assuming that I don't need to worry about >> them? >> >> Thanks, >> >> -- >> >> Alan Davey >> [EMAIL PROTECTED] >> 212-604-0200 x106 >> >> >> >> -- >> Please see the official ORACLE-L FAQ: >> http://www.orafaq.com >> -- >> Author: Alan Davey >> 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). > > >__________________________________________________ >Do You Yahoo!? >Sign up for SBC Yahoo! Dial - First Month Free >http://sbc.yahoo.com >-- >Please see the official ORACLE-L FAQ: http://www.orafaq.com >-- >Author: Jack Silvey > 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: Alan Davey 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).
