drop the existing index and recreate on different columns

d27m11y
d27m11y used Ask the Experts™
on
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!
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Most Valuable Expert 2012
Distinguished Expert 2018
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.

Author

Commented:
How do we revalidate?
Most Valuable Expert 2011
Top Expert 2012
Commented:
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.
Ensure you’re charging the right price for your IT

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden using our free interactive tool and use it to determine the right price for your IT services. Start calculating Now!

Most Valuable Expert 2012
Distinguished Expert 2018

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.
Most Valuable Expert 2012
Distinguished Expert 2018

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.
Most Valuable Expert 2011
Top Expert 2012
Commented:
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.
Most Valuable Expert 2012
Distinguished Expert 2018

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.
Most Valuable Expert 2011
Top Expert 2012

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

Author

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).
Most Valuable Expert 2011
Top Expert 2012
Commented:
no you can't change the columns of an index with an "alter"

you must build a new one.

Author

Commented:
Was able to drop and recreate the index.
Steve WalesSenior Database Administrator

Commented:
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

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial