Why does moving a table invalidate the index?

[Mr.Hanky]

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

[marist89]

yes, the rowid may change.

[davey23uk]

yep the rowid changes

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)

[Mr.Hanky]

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.

[marist89]

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...

[Mr.Hanky]

For what it is worth the correct OEM term is "unusable".

[slimdave]

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.

[DaPi]

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!)

[slimdave]

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.