on 'MOVE' and 'REBUILD'

[edsperez]

Guys,

I need your opinions coupled with experience on this.

I tried using these two commands to relocate tables and indexes on a different tablespace. What happen if you don't transfer it to a different tablespace and just do a 'move' and 'rebuild' on the same tablespace, will it resolve table fragmentation?

Thanks,

Ed

[pando]

not really, it depends how bad is your tablespace fragmentation, if it´s bad then there is no point moving, rebuilding in same tablespace

[gpsingh]

I would prefer to move to a seperate tablespace, coalesce the old tablespace and then move it back.
Big process but effective.
U can do it on the same tablespace, if the table is small or if u need to
change the storage paremters.

[marist89]

Better still:
1. create new_ts as LMT
2. move/rebuild your tables/indexes there
3. don't ever worry about fragmentation again

[vijay8282]

what i think is that

only rebuild will reduce fragmentation and move will not do that. if youmove after that you hv to rebuild to defragment the table/index.

letc c what gurus say.

[gpsingh]

Just to clarify,

Table rebuild is available in 9i and only move is available in 8i.