3 Replies Latest reply: Jun 17, 2013 3:32 AM by leventozkan RSS

    How can i disable/enable indexes in ODI?

    leventozkan

      Hello all,

       

      I want to disable indexes before loading fact table. After loading process i want to enable again. How can i implement this in ODİ? Are there any knowledge module or odiRef function?

       

      ODI 11g 11.1.1.6

      Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - 64bit Production

       

      Thanks.

        • 1. Re: How can i disable/enable indexes in ODI?
          user5912565

          Two options:

           

          a)

          - create/call an ODI procedure to drop the index

          - run the interface

          - create/call an ODI procedure to create the index

           

          b)

          - create/call an ODI procedure to mark your index usable: alter index your_index unusable;

          - run the interface

          - create/call an ODI procedure to mark your index usable, you must rebuild the index: alter index your_index rebuild [online];

          • 2. Re: How can i disable/enable indexes in ODI?
            JeromeFr

            Procedure is a way to do that but I personally prefer to add these steps to KMs.

             

            I've a step before the insertion which is :

             

            BEGIN
            ODI_TOOLS.DISABLE_INDEXES('<%=odiRef.getTargetTable("RES_NAME")%>');
            END;
            
            

             

            and another one after it :

             

            BEGIN
            ODI_TOOLS.REBUILD_INDEXES('<%=odiRef.getTargetTable("RES_NAME")%>');
            END;
            
            


            These procedures uses a cursor to query the dictionary tables and find all the indexes related to the table given as parameter, in order to disable/rebuild them. You can also code that directly in your KM.

             

             

            Hope it helps.

             

            Regards,

            JeromeFr

            • 3. Re: How can i disable/enable indexes in ODI?
              leventozkan

              I wrote two procedures.


              DISABLE_INDEXES:

                   On source:      select index_name from dba_indexes where table_name= '<%=odiRef.getTable("L","TARG_NAME","A")%> '

                   On target:         alter index  #index_name unusable

              REBUILD_INDEXES

                   On source:      select index_name from dba_indexes where table_name= '<%=odiRef.getTable("L","TARG_NAME","A")%> '

                   On target:         alter index  #index_name rebuild


              And i used these two procedures in my package.


              Many  thanks for advices.