3 Replies Latest reply on Apr 16, 2014 2:35 AM by Chetan-Oracle

    Delete unused indexes on DB2 LUW?


      We have a lot of unused (system) indexes on tables that I would like to inactivate and delete from the db. Has anyone done that?

      I saw this doc but it specifies DB2 z/OS only: Bookshelf v8.0: About Inactivating Unused Indexes

      Is there anyone that can help me?

        • 1. Re: Delete unused indexes on DB2 LUW?

          Dear User,


          Thank you for using Oracle communities.


          About your query on dropping Vanilla Index, please note that any manual modifications performed directly at database layer on the physical database schema are not supported.


          So to summaries:


          Custom Index :


          1. You may create and drop indexes and you can refer this to your developers.

          2. Dropping indexes may impact to Application performance in the future


          Vanilla Index


          1. Its not advice able to drop Vanilla Indexes as this will cause the Siebel Application in a UN-supported mode

          2. If you wish to do, you may engage the Expert Service( Billable) for more details.


          I hope it helps.




          Best Regards,


          • 2. Re: Delete unused indexes on DB2 LUW?

            Thanks Chetan,


            Not quite the answer I was looking for.

            Just to clarify the article I linked to above ONLY applies to z/OS?


            You have got me interested with point 2: engage the Expert Service, what would they be able to do if deleting indexes in not recommended or supported?




            • 3. Re: Delete unused indexes on DB2 LUW?

              Hello Daniel,


              Yeah article applies to DB2 z/OS only.


              Expert services will analyse the situation and if that part of functionality or piece of application is not using that index and if does not has overall impact on application, they may give you green signal after through analysis.

              Since proactive analysis is something support does not do, we normally recommend to have someone who can physically analyse the situation and apply the requirement from client and expert services are better candidates for them.


              I hope it helps you.



              Best Regards,