Home » Other » General » drop indexes while in use (11.2.0.4)
drop indexes while in use [message #676656] Fri, 28 June 2019 13:34 Go to next message
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.
Re: drop indexes while in use [message #676657 is a reply to message #676656] Fri, 28 June 2019 13:51 Go to previous messageGo to next message
BlackSwan
Messages: 26567
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

Re: drop indexes while in use [message #676658 is a reply to message #676656] Fri, 28 June 2019 14:08 Go to previous messageGo to next message
Michel Cadot
Messages: 66475
Registered: March 2007
Location: Nanterre, France, http://...
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.

Re: drop indexes while in use [message #676659 is a reply to message #676658] Fri, 28 June 2019 15:16 Go to previous messageGo to next message
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 Go to previous messageGo to next message
Michel Cadot
Messages: 66475
Registered: March 2007
Location: Nanterre, France, http://...
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?

Re: drop indexes while in use [message #676661 is a reply to message #676659] Sat, 29 June 2019 04:32 Go to previous messageGo to next message
John Watson
Messages: 7956
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.
Re: drop indexes while in use [message #676731 is a reply to message #676661] Tue, 09 July 2019 13:12 Go to previous message
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;
Previous Topic: How to call a tablespace and a proprietary user
Next Topic: DB 12.2 New Features Guide
Goto Forum:
  


Current Time: Tue Jul 23 04:28:46 CDT 2019