Forum Stats

  • 3,781,172 Users
  • 2,254,486 Discussions
  • 7,879,604 Comments

Discussions

Skip a particular Index

754320
754320 Member Posts: 22
edited Feb 17, 2010 11:15AM in General Database Discussions
A quick one about Oracle Indexes....

Is it possible to disable a particular index (for a period of time) but still keep it online such that all changes are still updated in the index but it is not used during queries? I don't want to rebuild the index when I enable it again.
Tagged:

Best Answer

  • Uwehesse-Oracle
    Uwehesse-Oracle Member Posts: 1,767
    Accepted Answer
    >
    Is it possible to disable a particular index (for a period of time) but still keep it online such that all changes are still updated in the index but it is not used during queries? I don't want to rebuild the index when I enable it again.
    >

    Ther is a New Feature introduced in 11g exactly for this:
    SQL> alter index <name> invisible;
    This will make the index invisible for the optimizer, but DML will still maintain the index. Main purpose for this is, you can check whether the index is needed before you decide to actually drop it. Should somebody complain, you bring it back instantly with
    SQL> alter index <name> visible;
    Kind regards
    Uwe

    http://uhesse.wordpress.com

Answers

  • Neev
    Neev Member Posts: 490
    edited Feb 17, 2010 5:38AM
    alter index <indexname> unusable;

    Or visit

    http://www.stanford.edu/dept/itss/docs/oracle/10g/server.101/b10759/statements_1008.htm
  • Hi,

    use the hint NO_INDEX on your query.
    SELECT /*+NO_INDEX(emp emp_empno)*/ empno 
    FROM emp 
    WHERE empno > 200;
    If this hint specifies a single available index, then the optimizer does not consider a scan on this index. Other indexes not specified are still considered.

    Go trought the Optimizer Hints on the performance Tuning Guide

    Regards,
    Vijayaraghavan K
  • MsJ
    MsJ Member Posts: 936
    user9511474 wrote:
    A quick one about Oracle Indexes....

    Is it possible to disable a particular index (for a period of time) but still keep it online such that all changes are still updated in the index but it is not used during queries? I don't want to rebuild the index when I enable it again.
    ==

    SELECT /*+ FULL(e) */ employee_id, last_name
    FROM employees e
    WHERE last_name LIKE :b1;

    Oracle performs a full table scan on the employees table to execute this statement, even if there is an index on the last_name column that is made available by the condition in the WHERE clause.
  • 754320
    754320 Member Posts: 22
    Thanks a lot for your comments.

    However I don't want to modify any query....I was asking whether it is possible to do it at database level since I am unable to modify the application.
  • 754320
    754320 Member Posts: 22
    this is not what I require...like this the index will not get updated with any changes to the table. To bring it back online you have to rebuild it from scratch
  • Hi,

    Let me explain your requirement, kindly correct me my understanding is correct.

    If inserts or updates happen , then the it should update indexes
    But this indexes should not to be used by queries

    If the above is the case then it is not possible to set at database level.

    If you made index unusable, you will get the error ORA-01502 if the query tries to access the index.

    Regards,
    vijayaraghavan K
  • 754320
    754320 Member Posts: 22
    Confirmed...that what I was after:

    having the index updated with any inserts or updates but not used during queries (without modifying the quereis)

    Anyone has any idea whether such functionality might be avaiable in 11g?

    Ludwig
  • USER101
    USER101 Member Posts: 956 Bronze Badge
    Try the SKIP_UNUSABLE_INDEXES parameter. The parameter will basically ignore any indexes that are in unusable state.

    Read the Metalink doc 281500.1
  • 754320
    754320 Member Posts: 22
    if an index is unusable you need to rebuild it to bring it back online...That is not what I asked for.

    Thanks for your feedback
  • Uwehesse-Oracle
    Uwehesse-Oracle Member Posts: 1,767
    Accepted Answer
    >
    Is it possible to disable a particular index (for a period of time) but still keep it online such that all changes are still updated in the index but it is not used during queries? I don't want to rebuild the index when I enable it again.
    >

    Ther is a New Feature introduced in 11g exactly for this:
    SQL> alter index <name> invisible;
    This will make the index invisible for the optimizer, but DML will still maintain the index. Main purpose for this is, you can check whether the index is needed before you decide to actually drop it. Should somebody complain, you bring it back instantly with
    SQL> alter index <name> visible;
    Kind regards
    Uwe

    http://uhesse.wordpress.com
This discussion has been closed.