asked on drop the existing index and recreate on different columns
I have a history table that contains 3 months of data. Can I drop the existing index built on (a, b, c) and recreate an index on different columns like on (a, b, d, e). Does this affect data. Are there any steps that I have to follow in sequence.
What are the required steps that I have to follow.
Please advise asap!
What are the required steps that I have to follow.
Please advise asap!
Oracle Database
Last Comment
Steve Wales
ASKER CERTIFIED SOLUTION
THIS SOLUTION ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
ASKER
How do we revalidate?
SOLUTION
THIS SOLUTION ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
Check out: DBMS_UTILITY.COMPILE_SCHEM
http://docs.oracle.com/cd/E11882_01/appdev.112/e40758/d_util.htm#ARPLS73226
or you can connect as a privileged account and run utlrp whick tries to compile ALL objects in dependency order.
http://docs.oracle.com/cd/E11882_01/appdev.112/e40758/d_util.htm#ARPLS73226
or you can connect as a privileged account and run utlrp whick tries to compile ALL objects in dependency order.
>>No invalidation of objects should occur due to an index build.
They are dropping not rebuilding.
https://docs.oracle.com/cd/B28359_01/server.111/b28286/statements_8016.htm
They are dropping not rebuilding.
https://docs.oracle.com/cd/B28359_01/server.111/b28286/statements_8016.htm
When you drop an index, Oracle Database invalidates all objects that depend on the underlying table, including views, packages, package bodies, functions, and procedures.
SOLUTION
THIS SOLUTION ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
I guess I stand corrected. It used to be that way. I lived it!
I cannot say if 11.1 is a doc bug or not.
Anyway, guess object invalidation is not longer a thing on dropped indexes.
I cannot say if 11.1 is a doc bug or not.
Anyway, guess object invalidation is not longer a thing on dropped indexes.
11.2 adds a lot of invalidation protection.
If you change part of a package, objects dependent on the other parts should not invalidate.
You can still get "existing state discarded" errors if you happen to already have the package in memory, but that makes sense.
If you change part of a package, objects dependent on the other parts should not invalidate.
You can still get "existing state discarded" errors if you happen to already have the package in memory, but that makes sense.
ASKER
or can I alter index which was built on (a, b, c) to an index on different columns like on (a, b, d, e).
SOLUTION
THIS SOLUTION ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
ASKER
Was able to drop and recreate the index.
No comment has been added to this question in more than 21 days, so it is now classified as abandoned.
I have recommended this question be closed as follows:
Split:
-- slightwv (https:#a41525439)
-- sdstuber (https:#a41525452)
-- sdstuber (https:#a41525462)
-- sdstuber (https:#a41525582)
If you feel this question should be closed differently, post an objection and the moderators will review all objections and close it as they feel fit. If no one objects, this question will be closed automatically the way described above.
sjwales
Experts-Exchange Cleanup Volunteer
I have recommended this question be closed as follows:
Split:
-- slightwv (https:#a41525439)
-- sdstuber (https:#a41525452)
-- sdstuber (https:#a41525462)
-- sdstuber (https:#a41525582)
If you feel this question should be closed differently, post an objection and the moderators will review all objections and close it as they feel fit. If no one objects, this question will be closed automatically the way described above.
sjwales
Experts-Exchange Cleanup Volunteer