Home » RDBMS Server » Server Administration » Drop Index (Oracle 10g - AIX)
Drop Index [message #364971] Wed, 10 December 2008 13:29 Go to next message
prashanthgs
Messages: 89
Registered: May 2005
Location: chennai
Member
Hi,

We are having table A which appr. 20million records inserted per day.

We created index on one of the column to make it faster. We heard from Dev team that now loading performance got decreased.

We decided to test in followind scenario:

a. Load data into table from different session.
b. Drop index
c. Load data into table.

Compare the timings of Load.

But we are strucked while Dropping Index.

DROP INDEX INDEXNAME;

Error 1:
ERROR at line 1:
ORA-00054: resource busy and acquire with NOWAIT specified

we tried FORCE OPTION

DROP INDEX INDEXNAME FORCE;

ERROR at line 1:
ORA-29862: cannot specify FORCE option for dropping non-domain index

What are the alternative ways to do our test and to drop the Index.

Thanks and Regards
Prashanth



Re: Drop Index [message #365051 is a reply to message #364971] Wed, 10 December 2008 13:36 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
You have no way but wait no one use the index.
Ot you can load in direct mode.

Regards
Michel
Re: Drop Index [message #365053 is a reply to message #364971] Wed, 10 December 2008 13:39 Go to previous message
Mahesh Rajendran
Messages: 10672
Registered: March 2002
Location: oracleDocoVille
Senior Member
Account Moderator
Expected behavior.
Presence of Index may help with queries, but will hinder DML operations.
Depending on your need/methods You can

*Disable/drop the indexes
*use direct load methods to load data
*enable/build indexes.

Both disabling indexes or just dropping the index has both pros and cons.
Search this forum/googe/docs.
Previous Topic: wht is the best solution??
Next Topic: Memory parameter not included in sga
Goto Forum:
  


Current Time: Sun Dec 04 08:32:42 CST 2016

Total time taken to generate the page: 0.18178 seconds