-
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.
-
yes, the rowid may change.
Jeff Hunter
-
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)
-
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.
-
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
-
For what it is worth the correct OEM term is "unusable".
I remember when this place was cool.
-
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.
-
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!)
-
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.
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
|