11 Replies Latest reply: Feb 2, 2012 9:59 PM by HuaMin Chen RSS

    How to disable the index in oracle

    317546
      Hi,

      Is there any way we can disbal the index in oracle.
      For the performance issue,I want to disable some of the indexes which i have created earlier. I dont want to drop the index.

      I tried the below command
      Alter Index <Schema>.<Index_name> Disable;

      I am getting the below error message:
      ORA-02243: invalid ALTER INDEX or ALTER MATERIALIZED VIEW option


      Thanks,
      Krishna
      .
        • 2. Re: How to disable the index in oracle
          581608
          You can not disable the Index. You can make it unusable.

          Check the URL given above for more details.

          Regards
          RK
          • 3. Re: How to disable the index in oracle
            MichaelS
            You can not disable the Index.
            You can - if it is a function based index:
            SQL>  create index emp_idx on emp (upper(ename))
            /
            Index created.
            
            SQL>  alter index emp_idx disable
            /
            Index altered.
            
            SQL>  alter index emp_idx enable
            /
            Index altered.
            • 4. Re: How to disable the index in oracle
              581608
              Thank you Michels for pointing this!!

              Regards
              RK
              • 5. Re: How to disable the index in oracle
                601585
                Another way is to mark the index unsable.
                alter index your_index unusable;
                To remark your index usable, you must rebuild the index.(This sucks)
                alter index your_index rebuild [online];
                Check following parameter. If your index is marked unusable and "skip_unusable_indexes" is false, you DML will fail.
                show parameter skip_unusable_indexes;
                alter session set skip_unusable_indexes = true;
                • 6. Re: How to disable the index in oracle
                  615440
                  But if its a unique index then even if the parameter SKIP_UNUSABLE_INDEXES=True, DmL Wont work
                  • 7. Re: How to disable the index in oracle
                    411951
                    HI,

                    Somesh,
                    I think it is one of the thousands bugs in oracle.

                    1.) If i create unique constraint (oracle internaly creates unique or non unique index for it depending if constrainti is deferreble)
                    i can dissable the constraint and insert the data.

                    2.) If i create the unique index and set it unusable oracle wont let to insert data.
                    • 8. Re: How to disable the index in oracle
                      795824
                      hi,

                      i am presently doning mutilple files checkin to oracle UCM,

                      i am presently checked in 100 files in UCM using java api (provided in loop condition)

                      after executed the checkin page, it was able to checked in to UCM. but many files being indexed.

                      so, after checkedin the documents inside UCM, i wanted to view the all the documents immediatly. all the documents should publish/relase immediatly after checkedin done.

                      here i need to know how to stop the indexing in ucm after checking the documents.

                      how to disable the indexer, if i disable the indexer when documents being indexed is there impact will be happend to content server ?

                      how to save the time for avoiding indexing the all the documents.

                      please help me out . i need to know very urgent
                      • 9. Re: How to disable the index in oracle
                        782117
                        Hello,
                        I have a question related to disabing index and thought I would post it in thread.

                        I'm altering a column length to increase the size and getting "ORA-30556: functional index is defined on the column to be modified".
                        On searching more about this error, it seems like the function index must be dropped before altering the column.
                        The table I'm dealing with is huge.

                        Question 1:
                        In case of dropping and recreating the index, should the following steps be done:
                        - Drop Index
                        - Alter the column to increase the size
                        - Recreate the index with NOLOGGING and NOPARALLEL clause
                        - ALTER INDEX to turn on LOGGING
                        - Gather Statistics on that index

                        Question 2:
                        Is there anything else that should be done when the index is dropped and re-created?

                        Question 3:
                        What are the side-effects of carrying out the above steps in a huge table with around 15 million rows?

                        Question 4:
                        Would it work if I disable the index, alter the column and reenable the index?
                        Do I have to rebuild the index and gather Stats upon reenabling it?


                        Thanks!
                        • 10. Re: How to disable the index in oracle
                          rp0428
                          Just so everyone knows this last question is posted over here now: ORA-30556: functional index is defined on the column to be modified

                          MichaelS said:
                          >
                          alter index emp_idx disable
                          >
                          That solution won't work for this use case.
                          The statement works but you still get the original error if you try to alter the col.

                          Same applies for Dion's suggestion:
                          alter index your_index unusable;
                          Still can't alter the column.
                          • 11. Re: How to disable the index in oracle
                            HuaMin Chen
                            Why not to temporarily drop that and later on recreate it?

                            Regards,