Index compression disabled after export import.

[former_member211629]

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

[former_member289242]

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

[former_member211629]

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,

[Former Member]

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

[thiago_cavalheiro]

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

[former_member211629]

Hi Thiago,

Thanks for the reference of notes. But this is a work around. You said import does not compresses the indexes..but I am bit unlucky to find out and come across some SAP published document saying so.

Do you some reference available.

Regards,

Soumen