7 Replies Latest reply: Aug 13, 2007 9:03 AM by John Spencer RSS

    How to Drop an index from a used table. online

    592315
      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
        • 1. Re: How to Drop an index from a used table. online
          ajallen
          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.
          • 2. Re: How to Drop an index from a used table. online
            592315
            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
            • 3. Re: How to Drop an index from a used table. online
              John Spencer
              "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
              • 4. Re: How to Drop an index from a used table. online
                ajallen
                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.
                • 5. Re: How to Drop an index from a used table. online
                  Laurent Schneider
                  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
                  • 6. Re: How to Drop an index from a used table. online
                    592315
                    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.
                    • 7. Re: How to Drop an index from a used table. online
                      John Spencer
                      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