Thanks to all who replied.
Fortunately, the tables are relatively small, so the rebuild of the indicies shouldn't take very long.
--
Alan Davey
adavey_at_competitrack.com
212-604-0200 x106
On 7/9/02, Jack Silvey <jack_silvey_at_yahoo.com> 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 <adavey_at_competitrack.com> 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
>> adavey_at_competitrack.com
>> 212-604-0200 x106
>>
>>
>>
>> --
>> Please see the official ORACLE-L FAQ:
>> http://www.orafaq.com
>> --
>> Author: Alan Davey
>> INET: adavey_at_competitrack.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).
>
>
>__________________________________________________
>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: jack_silvey_at_yahoo.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: Alan Davey
INET: adavey_at_competitrack.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 Tue Jul 09 2002 - 12:43:32 CDT
