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).
