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:
- 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:
- 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.
- Use parallel (degree x) to rebuild, since it will
spawn off more processes and take less time. see space
considerations above.
- Use nologging, unless you want to store your
indexes in the redo logs (otherwise, you can always
just recreate, much easier)
- use the "compute statstics" clause to gather stats
during the build
- 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.
- 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).
Received on Tue Jul 09 2002 - 12:24:36 CDT
