How to Drop an index from a used table. online
Posted: 2007-8-9 上午7:50

I need to drop an index from a table that is constantly used , read and write.
The DML's are locking the table and preventing the index drop (ORA-00054: resource busy and acquire with NOWAIT specified )

I tried to lock the table first - using 'lock table TAB1 in exclusive mode'
but when i ran the 'drop index' command , the drop (as all DDL's) first commits and this frees the lock which cause again the ORA-00054: resource busy and acquire with NOWAIT specified error.

I would appriciate any help .I can't take the DB or table or Application writing to the table offline. I need something like 'create index IND1 online' for DROP but there is none as far as I know...

Thanks in advanve.
Amit Zor

ajallen

Posts: 1,609
Registered: 11/22/99

Re: How to Drop an index from a used table. online
Posted: 2007-8-9 上午8:38

in response to: user589312

Reply

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.

user589312

Posts: 7
Registered: 08/09/07

Re: How to Drop an index from a used table. online
Posted: 2007-8-9 上午11:40

in response to: user589312

Reply

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.

Amit

John Spencer

Posts: 6,366
Registered: 07/09/99

Re: How to Drop an index from a used table. online
Posted: 2007-8-9 下午12:53

in response to: user589312

Reply

"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.

John

ajallen

Posts: 1,609
Registered: 11/22/99

Re: How to Drop an index from a used table. online
Posted: 2007-8-9 下午2:35

in response to: user589312

Reply

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.

Laurent Schneider

Posts: 5,412
Registered: 02/25/01

Re: How to Drop an index from a used table. online
Posted: 2007-8-10 上午3:21

in response to: user589312

Reply

maybe you can try

alter session set ddl_lock_timeout = 1000000;
drop index blabla;

but of course this will work not work on old releases...

Message was edited by:
Laurent Schneider
DDL_LOCK_TIMEOUT 11g reference

user589312

Posts: 7
Registered: 08/09/07

Re: How to Drop an index from a used table. online
Posted: 2007-8-12 上午2:49

in response to: user589312

Reply

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.

Amit.

John Spencer

Posts: 6,366
Registered: 07/09/99

Re: How to Drop an index from a used table. online
Posted: 2007-8-13 上午7:03

in response to: user589312

Reply

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?

John

come from：http://forums.oracle.com/forums/thread.jspa?threadID=541834

ITPub博客

How to Drop an index from a used table. online

最新文章