1 Reply Latest reply: Feb 27, 2012 9:00 PM by Thiago Cavalheiro RSS

Index compression disabled after export import.

Soumen Chattopadhyay
Currently Being Moderated

Hi All,

 

I am having following problem.

 

I have an SAP installation with Oracle 10G Database which grewup since 5 years. We are doing export / import to deal with fragmentation, get a fresh Oracle 11G database with all its available compression features.

 

I did compressed indices in my source system, but when I am importing the same on the target system, I see index compression status as disabled. During importing in the target system I am selectting all the tablespaces for compression, this resulted greatly, found all my tables got compressed but indexes not.

 

Can anybody help me out? Points guranteed for right guide.

 

Regards,

Soumen

  • Re: Index compression disabled after export import.
    Thiago Cavalheiro
    Currently Being Moderated

    Hello Soumen,

     

    Actually indexes are not compressed upon database import, so this is something that have to be done manually after. This was designed this way because you cannot simply compress all database's indexes. You will need to evaluate which of them should be compressed after (even if they were compressed on the source system) and act in order to compress them again.

    There are cases where the compressed version is bigger than an uncompressed, and even BR*Tools has a way to measure that, as can be seen on note[1464156|http://service.sap.com/sap/support/noteshttp://service.sap.com/sap/support/notes/1464156].

     

    Hope that clarifies.

     

    Best regards,

    Thiago

  • Re: Index compression disabled after export import.
    Gunjan Tomar
    Currently Being Moderated

    Hello Soumen,

     

    If index compression is activated, each index key must be stored only once - regardless of how often it actually occurs. This can significantly reduce the size of the index in the case of indexed columns that have a small number of occurrences. For more information, see Note 1109743.

     

    Hope above information helps.

     

    Best Regards,

    Gunjan

    • Re: Index compression disabled after export import.
      Soumen Chattopadhyay
      Currently Being Moderated

      Hi Gunjan,

       

      Sorry to say, my feeling is you didn't understood my question. The index compression etc are all welknown feature of Oracle what is aceepeted by SAP also.

       

      My question is -


       

      I am having a source system where index is already compressed. I am copying the system to a target system using Database independent system copy method, but in the target machine index are not compressed.

       

      So your hint is actually not the solution.

       

      Regards,

      • Re: Index compression disabled after export import.
        Michael Hofmänner
        Currently Being Moderated

        Hi

         

        You didn't specify how you exported and imported the database. I think you used sapinst. On newer SAP releases the report SMIGR_CREATE_DDL recognizes compressed indexes and generates *.SQL files that can be reused during the import.

         

        You simply have to call the report prior to exporting the system and copy the *SQL files to this dir <your export location>/ABAP/DB/ORA

         

        The import will scan for the files and use the DDL they contain. If your system is too old, you can create your own files, if a certain table has tabart APPL1 you need to create a file APPL1.SQL, example BSIS (bunch of lines removed for better readability):

         

        #
        # ORACLE : NATIVE SQL EXPORT GENERATED AT 20120229082243 
        #
        
        tab: BSIS
        sql:    CREATE TABLE "BSIS"
              ("MANDT" VARCHAR2 (000003)
                DEFAULT '000' NOT NULL,
               "BUKRS" VARCHAR2 (000004)
                DEFAULT ' ' NOT NULL,
        ...
               "EBELP" VARCHAR2 (000005)
                DEFAULT '00000' NOT NULL)
              PCTFREE 10
              PCTUSED 00
              INITRANS 001
              TABLESPACE &APPL1&
           STORAGE (INITIAL     0001740800 K
                       NEXT        0001740800 K
                       MINEXTENTS  0000000001
                       MAXEXTENTS  UNLIMITED
                       PCTINCREASE 0000
                       FREELISTS   001
                       FREELIST GROUPS 01) ;
        
        ind: BSIS~0
        sql:     CREATE UNIQUE INDEX "BSIS~0" ON "BSIS"
                 ("MANDT",
                  "BUKRS",
                  "HKONT",
                  "AUGDT",
                  "AUGBL",
                  "ZUONR",
                  "GJAHR",
                  "BELNR",
                  "BUZEI")
               PCTFREE 10
               INITRANS 002
               TABLESPACE &APPL1&
           COMPRESS 5
           STORAGE (INITIAL     0000000064 K
                        NEXT        0000000064 K
                        MINEXTENTS  0000000001
                        MAXEXTENTS  UNLIMITED
                        PCTINCREASE 0000
                        FREELISTS   001) ;
        ...

         

        Cheers Michael

Actions