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.
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.
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.
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.
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) ; ...