OraFAQ Forum: General » drop indexes while in use

Home » Other » General » drop indexes while in use (11.2.0.4)

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

drop indexes while in use [message #676656]

Fri, 28 June 2019 13:34

philipebe
Messages: 19
Registered: September 2017

Junior Member

Hi,

Can I drop indexes and re-create them, while those indexes have been picked by optimizer for a sql execution and being used?
Do we need to shutdown the apps connected to the databases, before dropping indexes in a database?
Or can indexes be dropped while apps are accessing these indexes being dropped?

Please help me understand.
Thanks.

Report message to a moderator

Re: drop indexes while in use [message #676657 is a reply to message #676656]

Fri, 28 June 2019 13:51

BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal

Senior Member

>Can I drop indexes and re-create them
Yes, you can.
You can also poke yourself in the eye with a sharp pencil.
However you are advised not to do either of the above.

Please post SQL & results that show what is gained by dropping & rebuilding INDEX?

Please read and follow the forum guidelines, to enable us to help you:
OraFAQ Forum Guide
How to use {code} tags and make your code easier to read

Report message to a moderator

Re: drop indexes while in use [message #676658 is a reply to message #676656]

Fri, 28 June 2019 14:08

Michel Cadot
Messages: 68737
Registered: March 2007
Location: Saint-Maur, France, https...

Senior Member
Account Moderator

When you drop an index all execution plans using it are invalidated and the statements will be re-parsed the next time.
If a statement using the index is executing when the DROP INDEX is performed, depending on the version and configuration, this later will be blocked or received an error "ORA-00054: "resource busy and acquire with NOWAIT specified or timeout expired".

Now, you have to think, if your application does not need the index then there is no problem to drop it and then why recreating it?
If the application needs the index, it will obviously be in trouble if you drop the index.

Report message to a moderator

Re: drop indexes while in use [message #676659 is a reply to message #676658]

Fri, 28 June 2019 15:16

philipebe
Messages: 19
Registered: September 2017

Junior Member

the reason we are dropping and re-creating this index is,
for example... we have indexA on (columnA)
we want to drop this indexA and recreate indexA on (columnA, columnB).
so we dont want to create a concatenated index which has the first column from indexA.

[Updated on: Fri, 28 June 2019 15:16]

Report message to a moderator

Re: drop indexes while in use [message #676660 is a reply to message #676659]

Sat, 29 June 2019 00:08

Michel Cadot
Messages: 68737
Registered: March 2007
Location: Saint-Maur, France, https...

Senior Member
Account Moderator

First create the new index then drop the previous one.

But database objects are part of the application code.
Would you change the code of the application when the application is running?

Report message to a moderator

Re: drop indexes while in use [message #676661 is a reply to message #676659]

Sat, 29 June 2019 04:32

John Watson
Messages: 8965
Registered: January 2010
Location: Global Village

Senior Member

Th optimizer tends to favour composite indexes over single column indexes (a questionable preference, it dates back to the old rule based optimizer) so once your new indexes are created the old ones should fall into disuse and you can drop them no problem.
Note that if you have Enterprise Edition licences you can create the indexes with the ONLINE keyword. If you don't do this you will have table locks while the creation is in progress.

Report message to a moderator

Re: drop indexes while in use [message #676731 is a reply to message #676661]

Tue, 09 July 2019 13:12

philipebe
Messages: 19
Registered: September 2017

Junior Member

Thanks everyone for your response.
For now i decided to create a new index online with the 2 columns, and then drop the existing index with the one column (after testing in the DEV environment)
And i am creating the index online with nologging and parallel.
And then alter the index with logging and noparallel.

for example....if we have index a on table a column a, and i want to create index on table a with (column a,b), then this is what i did:
create index b on table a(col a,b) online parallel nologging;
alter index b noparallel logging;
drop index a;

Report message to a moderator

Previous Topic:	How to call a tablespace and a proprietary user
Next Topic:	Oracle support: from where?

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Sun Feb 16 21:08:18 CST 2025