moving tables...do i need to rebuild my indexes??
DBAsupport.com Forums - Powered by vBulletin
Page 1 of 2 12 LastLast
Results 1 to 10 of 12

Thread: moving tables...do i need to rebuild my indexes??

  1. #1
    Join Date
    Dec 2000
    Posts
    15

    Question

    If I use the alter table move option will i need to rebuild my indexes????? please hlep....

    thanks,
    lacey

  2. #2
    Join Date
    Feb 2000
    Location
    Washington DC
    Posts
    1,843
    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.

  3. #3
    Join Date
    Dec 2000
    Location
    Ljubljana, Slovenia
    Posts
    4,439
    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?

  4. #4
    Join Date
    Dec 2000
    Location
    Ljubljana, Slovenia
    Posts
    4,439
    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?

  5. #5
    Join Date
    Aug 2000
    Location
    Belgium
    Posts
    342
    And how do we rebuild the indexes ??

    drop + create
    or
    alter index ... rebuild ....

    Regards
    Gert

  6. #6
    Join Date
    Feb 2000
    Location
    Washington DC
    Posts
    1,843
    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.

  7. #7
    Join Date
    Dec 2000
    Location
    Ljubljana, Slovenia
    Posts
    4,439
    [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?

  8. #8
    Join Date
    Feb 2000
    Location
    Washington DC
    Posts
    1,843

    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 ?

  9. #9
    Join Date
    Jun 2000
    Location
    Madrid, Spain
    Posts
    7,447
    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

  10. #10
    Join Date
    Feb 2000
    Location
    Washington DC
    Posts
    1,843

    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
  •  



Click Here to Expand Forum to Full Width