Why does moving a table invalidate the index?
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 9 of 9

Thread: Why does moving a table invalidate the index?

  1. #1
    Join Date
    Jan 2001
    Posts
    3,131

    Why does moving a table invalidate the index?

    Weird, the Oracle docs here do not mention this.
    I would think Oracle would update the row id.

    http://download-west.oracle.com/docs...ables.htm#6359
    I remember when this place was cool.

  2. #2
    Join Date
    Nov 2000
    Location
    greenwich.ct.us
    Posts
    9,092
    yes, the rowid may change.
    Jeff Hunter
    marist89@yahoo.com
    http://marist89.blogspot.com/
    Get Firefox!
    "I pledge to stop eating sharks fin soup and will not do so under any circumstances."

  3. #3
    Join Date
    Sep 2002
    Location
    England
    Posts
    7,333
    yep the rowid changes

    Code:
    Connected to:
    Oracle9i Enterprise Edition Release 9.2.0.5.0 - Production
    With the Partitioning, Oracle Label Security, OLAP and Oracle Data Mining options
    JServer Release 9.2.0.5.0 - Production
    
    SQL> create table t (dummy varchar2(20));
    
    Table created.
    
    SQL> insert into t values ('1');
    
    1 row created.
    
    SQL> commit;
    
    Commit complete.
    
    SQL> select rowid from t;
    
    ROWID
    ------------------
    AAAMYNAABAAAJ5CAAA
    
    SQL> alter table t move;
    
    Table altered.
    
    SQL> select rowid from t;
    
    ROWID
    ------------------
    AAAMYOAABAAAJ5KAAA  (slight change)
    
    SQL> alter table t move tablespace libra_data;
    
    Table altered.
    
    SQL> select rowid from t;
    
    ROWID
    ------------------
    AAAMYPAAVAAAEbsAAA  (bigger change)

  4. #4
    Join Date
    Jan 2001
    Posts
    3,131
    What kills me is that Oracle's docs make no mention of this.
    Even the 8i new features bokk says that the index definition is retained. You would think they would put a simple warning...

    * Moving a table will invalidate you index. *

    Friggin developers must have written the docs.
    I remember when this place was cool.

  5. #5
    Join Date
    Nov 2000
    Location
    greenwich.ct.us
    Posts
    9,092
    One more notch on the experience belt.

    I read it somewhere back when I was trying to figure out partitions and indexes, but I can't put my browser on it right now...
    Jeff Hunter
    marist89@yahoo.com
    http://marist89.blogspot.com/
    Get Firefox!
    "I pledge to stop eating sharks fin soup and will not do so under any circumstances."

  6. #6
    Join Date
    Jan 2001
    Posts
    3,131
    For what it is worth the correct OEM term is "unusable".
    I remember when this place was cool.

  7. #7
    Join Date
    Aug 2002
    Location
    Colorado Springs
    Posts
    5,253
    Originally posted by Mr.Hanky
    You would think they would put a simple warning...

    * Moving a table will invalidate you index. *
    We hold some truths to be self-evident.

    David Aldridge,
    "The Oracle Sponge"

    Senior Manager, Business Intelligence Development
    XM Satellite Radio
    Washington, DC

    Oracle ACE

  8. #8
    Join Date
    Nov 2002
    Location
    Geneva Switzerland
    Posts
    3,142
    Originally posted by marist89
    I read it somewhere back when I was trying to figure out partitions and indexes, but I can't put my browser on it right now...
    Interesting psychology. I went hunting but didn't find anything quickly. I think it's because I can't remember when I didn't know that! (Which with my memory might mean it was last week!)

  9. #9
    Join Date
    Aug 2002
    Location
    Colorado Springs
    Posts
    5,253
    Originally posted by marist89
    I read it somewhere back when I was trying to figure out partitions and indexes, but I can't put my browser on it right now...
    It gets a mention in relation to the movement of partitions, but not in relation to the movement of non-partitioned tables, I believe.
    David Aldridge,
    "The Oracle Sponge"

    Senior Manager, Business Intelligence Development
    XM Satellite Radio
    Washington, DC

    Oracle ACE

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