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!
d27m11yAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

slightwv (䄆 Netminder) Commented:
It doesn't affect the base table data.

It will invalidate all objects that rely on that index and table.  They should revalidate on the next use.  I've seen a massivly OLTP system crash and burn in the few seconds it took a stored procedure to revalidate itself.  Granted the code wasn't written all that well, but it still happened.

It may cause queries that rely on the old index to not perform as well as they did.

It all depends on your systems.

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
d27m11yAuthor Commented:
How do we revalidate?
sdstuberCommented:
No invalidation of objects should occur due to an index build.

If you have space to store both indexes, I recommend creating the new index as INVISIBLE.

Then, after creation, test some queries with that index by setting your session to be able to use invisible indexes.

If performance is as you expect, or at least better than the old index, then  set the new index VISIBLE
and after that, set the old index to INVISIBLE.

This will ensure your system is never left without a valid index.
You will get a period of time where the optimizer must reoptimize to utilize the new index and that is a "cursor" invalidation, but not an "object" invalidatin.
Your Guide to Achieving IT Business Success

The IT Service Excellence Tool Kit has best practices to keep your clients happy and business booming. Inside, you’ll find everything you need to increase client satisfaction and retention, become more competitive, and increase your overall success.

slightwv (䄆 Netminder) Commented:
Check out:  DBMS_UTILITY.COMPILE_SCHEMA

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.
slightwv (䄆 Netminder) Commented:
>>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
When you drop an index, Oracle Database invalidates all objects that depend on the underlying table, including views, packages, package bodies, functions, and procedures.
sdstuberCommented:
That's 11.1 which shouldn't be used anymore.

Check 11.2 documentation.

http://docs.oracle.com/cd/E11882_01/server.112/e41084/statements_8017.htm#SQLRF01510


I don't have an 11.1 db around, but I think the documentation was incorrect for that version anyway.
slightwv (䄆 Netminder) Commented:
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.
sdstuberCommented:
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.
d27m11yAuthor Commented:
or  can I alter index which was built on (a, b, c)  to an index on different columns like on (a, b, d, e).
sdstuberCommented:
no you can't change the columns of an index with an "alter"

you must build a new one.
d27m11yAuthor Commented:
Was able to drop and recreate the index.
Steve WalesSenior Database AdministratorCommented:
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
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Oracle Database

From novice to tech pro — start learning today.