| Drop Index [message #364971] |
Wed, 10 December 2008 13:29  |
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 #365053 is a reply to message #364971] |
Wed, 10 December 2008 13:39  |
 |
Mahesh Rajendran
Messages: 10707
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.
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
