-
If I use the alter table move option will i need to rebuild my indexes????? please hlep....
thanks,
lacey
-
I don't think so as Index DDL depends only on the table name and columns of the table. Until these columns and table name doesn't change you don't need to worry about the undexes.
-
Not quite so, Sreddy. Indexes on moved table are in UNUSABLE state and must be rebuild. Indeses contain pairs (index key) <-> (physical location of a row, aka ROWID). If you muve the table physical location of each and every row of a table is changed, so ROWIDIs in indexes are wrong until they are rebuild.
*Index must be rebuild after ALTER TABLE MOVE*
Jurij Modic
ASCII a stupid question, get a stupid ANSI
24 hours in a day .... 24 beer in a case .... coincidence?
-
One more thing about the need to rebuild the index....
[QUOTE][i]Originally posted by sreddy [/i]
[B]Until these columns and table name doesn't change you don't need to worry about the undexes. [/B][/QUOTE]
Table and column names have nothing to do with index rebuild. You can change the name of the table ("RENAME old TO new;") and the underlying indexes will still be valid. I don't know if 8.1.7 allready support renaming the columns of a table, but it is possible to do so (dangerous and unsupported, anyway) in any previous version by hacking the data dictionary - renaming a column doesn't affect indexes either. They are all still valid and usabal.
The reason for this is the fact that relations between objects in data dictionary are generaly not mede using object names, they rather relay on surrogate object IDs, which don't change during renaming, moving etc...
Jurij Modic
ASCII a stupid question, get a stupid ANSI
24 hours in a day .... 24 beer in a case .... coincidence?
-
And how do we rebuild the indexes ??
drop + create
or
alter index ... rebuild ....
Regards
Gert
-
Thanks a lot jmodic ! I appreciate it.
******* The reason for this is the fact that relations between objects in data dictionary are generaly not mede using object names, they rather relay on surrogate object IDs, which don't change during renaming, moving etc ********
Seems here I was confused with recreate and rebulid. Yes rebuild will be valid, which makes sence from above statement, Oracle internally manages database objects using object id.
-
[QUOTE][i]Originally posted by denevge [/i]
[B]And how do we rebuild the indexes ??
drop + create
or
alter index ... rebuild ....
Regards
Gert [/B][/QUOTE]
You can do it both ways, it's your choice. The second option is less resource consuming and faster, though.
Jurij Modic
ASCII a stupid question, get a stupid ANSI
24 hours in a day .... 24 beer in a case .... coincidence?
-
jmodic
Could you tell me,
RECREATING indexes situation other than changing the the columns on which index built ? I mean if we can play with storage parameters of index etc.,I don't see the reason to recreate em.
I mean what are the cons of rebuilding it ?
-
I dont think rebuild frees space (at least when I tried several months ago it didnt), I remembered we had 10 extents in one index segment after rebuild it was still 10 but by recreating it it dropped to 2
-
pando
I do have the option of 'coalescing' it or using 'deallocate unused' though to regain the space.
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|