When I moved a table from one TS to another, the indexes became invalid. why?
Printable View
When I moved a table from one TS to another, the indexes became invalid. why?
Because rowid of the rows in the table changes and index becomes unusable not invalid.
Relative file number changes...so does Block number....when u move a segment from one tablespace to other.
Due to which RowId changes & index will be holding OLD rowid so it goes to INVALID state.
Abhay.
thanks for the reply guys. is this issue solved in 9i?
U need to rebuild index in 9i as well...
Abhay.
Abhay, here i would like to comment that status values for indexes can be 'VALID' or 'UNUSABLE'. Other stored objects can be 'VALID' or 'INVALID.' Either all_indexes, DBA_INDEXES or USER_INDEXES should be querried for the status of indexes.Quote:
Originally posted by abhaysk
Due to which RowId changes & index will be holding OLD rowid so it goes to INVALID state.
HTH
Wonder why didn't Oracle take care of such small things in 9i. All it was supposed to do is rebuild the index whenever such operations happened. Wonder if there is any specific reason why they didn't do it (or did they overlook such issues !)Quote:
Originally posted by abhaysk
U need to rebuild index in 9i as well...
Abhay.
Amar I didnt literally mean INVALID...it was jus to empasize theat indexes wud be unused if u moved the table.Quote:
Originally posted by adewri
Abhay, here i would like to comment that status values for indexes can be 'VALID' or 'UNUSABLE'. Other stored objects can be 'VALID' or 'INVALID.' Either all_indexes, DBA_INDEXES or USER_INDEXES should be querried for the status of indexes.
HTH
Abhay.
Aah you mean to say "I literally mean INVALID" or did i get it wrong :)Quote:
Originally posted by abhaysk
Amar I didnt literally mean INVALID...
Abhay.
Good Q...i blv they wudnt have overlooked..Quote:
Originally posted by quester
Wonder why didn't Oracle take care of such small things in 9i. All it was supposed to do is rebuild the index whenever such operations happened. Wonder if there is any specific reason why they didn't do it (or did they overlook such issues !)
Probably oracle corp guys might have thought....i mean logically...
that when user wants to move a table, then he may want to recreate the index in diff tablespace...in that case it wud be uswise to rebuild itself...;):p
i am waiting for Jomodic to come up with resaons...
abhay.
u got it wrong;)Quote:
Originally posted by adewri
Aah you mean to say "I literally mean INVALID" or did i get it wrong :)
ITS "did not an alias for didnt"
When I've subjected tables to MOVE commands, I usually know which unuseable indexes are going to impact which sets of users (depending on their application components.)
Some indexes, I KNOW are superfluous to user requirements, as they don't use them - but the third party developer delivered them - so we keep them, just so we can't be accused of diverging from their delivered specification.
So.. my point is:
Rather than having long periods of downtime, where users are restricted from connecting to the system, I rebuild the major indexes that are used firstly... let them back in, and continue with the other rebuilds at my leisure.
Oracle 9i might be good... but I can't believe it can reach such logical conclusions by itself, so Oracle don't even let it try!
:-)
Just a hypothesis
- Tony.
AJW_ID01:
wat does ur point have to do with the ongoing thread...
i really dont understand...
Abhay.
It has relevance to the point raised as to why Oracle9i might not implicitly rebuild indexes associated with objects subjected to a MOVE command.
:-)
T.
might be a time thing.
if oracle automaticaly rebuilt the indexes when a table was moved this it'd end up taking longer and you dont want that. Maybe a nice option to specify that you want to rebuilt all indexes when you issue the alter table command would have been nice.
Human touch.. it's much better than automated thing when it comes to planned adhoc activities. We can allways rebuild the indexes at the time of out fliexibility.Quote:
Originally posted by quester
Wonder why didn't Oracle take care of such small things in 9i. All it was supposed to do is rebuild the index whenever such operations happened. Wonder if there is any specific reason why they didn't do it (or did they overlook such issues !)
if oracle did it automatically, you can bet it would be a serial process (rebuild first index, then second index, then third index etc), which would really suck.
You just need a little bit of PL/SQL to create a procedure that will invoke DBMS_JOB to rebuild them in parallel.