This content has been marked as final. Show 7 replies
Wondering here, why you need to drop the index? To rebuild it? It could not be because it is not needed -- it clearly is if you are getting an ORA-00054.
The index I want to drop does not hold all the columns I need for a certain query , so I created a new index with the same fields + the needed column and then tried to drop the old (shorter) index.
It is requsted for the removal of an ORDER BY clause from a CPU consuming query.
The extra field enables me to remove it as the result set comes back ordered by the index.
"It is requsted for the removal of an ORDER BY clause from a CPU consuming query.
The extra field enables me to remove it as the result set comes back ordered by the index"
It might do that today, but it is not guaranteed to do it tomorrow. Oracle may be able to retrieve the records ordered using that index, but I would leave it in your query if you are depending on the rows being ordered. The CBO is smart enough to not actually sort the rows (i.e. act on the ORDER BY clause) if it can get them sorted using the index, but if it decides to use another access path for one of many reasons your results will not be sorted without the ORDER BY.
Since the CBO seems to think that your existing index is useful (which is why you cannot get the lock on it), your only option is to wait for a period of really low activity on the database and try it then.
Never tried it this way and not able right now to setup the proper test conditions...
You might try marking the index unusable ALTER INDEX indexname UNUSABLE;
Then no transactions can use it, so you should be able to drop it.
Like I said, this may or may not work, but maybe worth a try.
maybe you can try
but of course this will work not work on old releases...
alter session set ddl_lock_timeout = 1000000; drop index blabla;
Message was edited by:
DDL_LOCK_TIMEOUT 11g reference
Thanks all for the assistance.
I tried the 'alter session set ddl_lock=100' but sadly it didnt work as I am using oracle 9i.
I tend to agree with John regading the fact it is risky to leave the ordering for the access path and index usage and decided to leave the query with the order by clause.
It is still an anigma though that oracle lets you create an index online but not to drop one - a fact that cause tables to hold old indexes that can be dropped only when downtime is performed , which cause unneccessary i/o.
Thanks again for the help.
Well, Oracle will let you drop an index on-line, as long as there is no active query using that index. Since you keep hitting locks when you try to drop that index, it implies to me that it is almost constantly used. Which further implies that it is , at least to the optimizer, a very useful index.
When you create an index, Oracle just needs to scan the table, just like any query, and then build the index, it does not require any exclusive locks so you will not have any problems. When you drop an index, Oracle needs an exclusinve lock on the whole index, which it cannot get if a query is using it. What would happen to an actve query using the index if Oracle let you drop the index without getting the exclusive lock first?