Tuesday, June 28, 2011

Compression level for Indexes in SAP-Oracle DB


Index compression feature in Oracle 10g and supported by SAP can free up more than 25% for DB space. SAP provides compression level for some of the indexes but for others, you need to run script provided by SAP. This script can take long time to complete. I am listing the compression level for almost all big tables. This I got after running the script in our environment and this can fit in any environment.
Note : parallel degree should be adjusted based on np. of CPUs available on the server.
***********************
alter index  “BSIM~0″ rebuild online compress 4 parallel 4 pctfree 1;
alter index  “BSIM~0″ noparallel;
alter index  “EDIDC~0″ rebuild online compress 1 parallel 4 pctfree 1;
alter index  “EDIDC~0″ noparallel;
alter index  “EDIDC~1″ rebuild online compress 2 parallel 4 pctfree 1;
alter index  “EDIDC~1″ noparallel;
alter index  “EDIDC~2″ rebuild online compress 3 parallel 4 pctfree 1;
alter index  “EDIDC~2″ noparallel;
alter index  “EDIDC~3″ rebuild online compress 2 parallel 4 pctfree 1;
alter index  “EDIDC~3″ noparallel;
alter index  “EDIDC~4″ rebuild online compress 3 parallel 4 pctfree 1;
alter index  “EDIDC~4″ noparallel;
alter index  “EKBE~0″ rebuild online compress 4 parallel 4 pctfree 1;
alter index  “EKBE~0″ noparallel;
alter index  “EKBE~A” rebuild online compress 3 parallel 4 pctfree 1;
alter index  “EKBE~A” noparallel;
alter index  “EKBE~B” rebuild online compress 2 parallel 4 pctfree 1;
alter index  “EKBE~B” noparallel;
alter index  “EKBE~ZA” rebuild online compress 3 parallel 4 pctfree 1;
alter index  “EKBE~ZA” noparallel;
alter index  “EKBE~ZE” rebuild online compress 3 parallel 4 pctfree 1;
alter index  “EKBE~ZE” noparallel;
alter index  “EKET~0″ rebuild online compress 2 parallel 4 pctfree 1;
alter index  “EKET~0″ noparallel;
alter index  “EKET~E” rebuild online compress 3 parallel 4 pctfree 1;
alter index  “EKET~E” noparallel;
alter index  “EKPO~0″ rebuild online compress 2 parallel 15 nologging pctfree 1;
alter index  “EKPO~0″ noparallel;
alter index  “EKPO~1″ rebuild online compress 7 parallel 15 nologging pctfree 1;
alter index  “EKPO~1″ noparallel;
alter index  “EKPO~A” rebuild online compress 3 parallel 15 nologging pctfree 1;
alter index  “EKPO~A” noparallel;
alter index  “EKPO~B” rebuild online compress 3 parallel 15 nologging pctfree 1;
alter index  “EKPO~B” noparallel;
alter index  “EKPO~FPL” rebuild online compress 2 parallel 15 nologging pctfree 1;
alter index  “EKPO~FPL” noparallel;
alter index  “EKPO~SRV” rebuild online compress 2 parallel 15 nologging pctfree 1;
alter index  “EKPO~SRV” noparallel;
alter index  “FAGLFLEXA~0″ rebuild online compress 5 parallel 15 nologging pctfree 1;
alter index  “FAGLFLEXA~0″ noparallel;
alter index  “FAGLFLEXA~2″ rebuild online compress 6 parallel 15 nologging  pctfree 1;
alter index  “FAGLFLEXA~2″ noparallel;
alter index  “FAGLFLEXA~3″ rebuild online compress 4 parallel 15 nologging  pctfree 1;
alter index  “FAGLFLEXA~3″ noparallel;
alter index  “FAGLFLEXA~4″ rebuild online compress 4 parallel 15 nologging  pctfree 1;
alter index  “FAGLFLEXA~4″ noparallel;
alter index  “FAGLFLEXA~6″ rebuild online compress 4 parallel 15 nologging  pctfree 1;
alter index  “FAGLFLEXA~6″ noparallel;
alter index  “FAGLFLEXA~ZN1″ rebuild online compress 5 parallel 15 nologging  pctfree 1;
alter index  “FAGLFLEXA~ZN1″ noparallel;
alter index  “VBFA~0″ rebuild online compress 3 parallel 4 pctfree 1;
alter index  “VBFA~0″ noparallel;
alter index  “VBFA~ZDL” rebuild online compress 1 parallel 4 pctfree 1;
alter index  “VBFA~ZDL” noparallel;
alter index  “MARCH~0″ rebuild online compress 2 parallel 4 pctfree 1;
alter index  “MARCH~0″ noparallel;
alter index  “MARDH~0″ rebuild online compress 2 parallel 4 pctfree 1;
alter index  “MARDH~0″ noparallel;
alter index  “MARD~0″ rebuild online compress 2 parallel 15 nologging  pctfree 1;
alter index  “MARD~0″ noparallel;
alter index  “MARD~ZA” rebuild online compress 2 parallel 15 nologging pctfree 1;
alter index  “MARD~ZA” noparallel;
alter index  “EKPO~0″ rebuild online compress 2 parallel 15 nologging pctfree 1;
alter index  “EKPO~0″ noparallel;
alter index  “EKPO~1″ rebuild online compress 7 parallel 15 nologging pctfree 1;
alter index  “EKPO~1″ noparallel;
alter index  “EKPO~A” rebuild online compress 3 parallel 15 nologging pctfree 1;
alter index  “EKPO~A” noparallel;
alter index  “EKPO~B” rebuild online compress 3 parallel 15 nologging pctfree 1;
alter index  “EKPO~B” noparallel;
alter index  “EKPO~FPL” rebuild online compress 2 parallel 15 nologging pctfree 1;
alter index  “EKPO~FPL” noparallel;
alter index  “EKPO~SRV” rebuild online compress 2 parallel 15 nologging pctfree 1;
alter index  “EKPO~SRV” noparallel;
alter index  “MBEWH~0″ rebuild online compress 4 parallel 15 nologging  pctfree 1;
alter index  “MBEWH~0″ noparallel;
alter index  “MBEW~0″ rebuild online compress 2 parallel 4 pctfree 1;
alter index  “MBEW~0″ noparallel;
alter index  “MBEW~ML1″ rebuild online compress 1 parallel 4 pctfree 1;
alter index  “MBEW~ML1″ noparallel;
alter index  “MBEW~ZBW” rebuild online compress 2 parallel 4 pctfree 1;
alter index  “MBEW~ZBW” noparallel;
alter index  “S012~0″ rebuild online compress 10 parallel 4 pctfree 1;
alter index  “S012~0″ noparallel;
alter index  “S012~VAB” rebuild online compress 2 parallel 4 pctfree 1;
alter index  “S012~VAB” noparallel;
alter index  “S013~0″ rebuild online compress 10 parallel 4 pctfree 1;
alter index  “S013~0″ noparallel;
alter index  “S031~0″ rebuild online compress 8 parallel 4 pctfree 1;
alter index  “S031~0″ noparallel;
alter index  “S033~0″ rebuild online compress 8 parallel 4 pctfree 1;
alter index  “S033~0″ noparallel;
alter index  “S033~A” rebuild online compress 1 parallel 4 pctfree 1;
alter index  “S033~A” noparallel;
alter index  “S033~B” rebuild online compress 3 parallel 4 pctfree 1;
alter index  “S033~B” noparallel;
alter index  “S120~0″ rebuild online compress 6 parallel 15 nologging  pctfree 1;
alter index  “S120~0″ noparallel;
alter index  “S120~VAB” rebuild online compress 2 parallel 15 nologging   pctfree 1;
alter index  “S120~VAB” noparallel;
alter index  “VBFA~0″ rebuild online compress 3 parallel 15 nologging pctfree 1;
alter index  “VBFA~0″ noparallel;
alter index  “VBFA~ZDL” rebuild online compress 1 parallel 15 nologging   pctfree 1;
alter index  “VBFA~ZDL” noparallel;
alter index  “VBRP~0″ rebuild online compress 2 parallel 4 pctfree 1;
alter index  “VBRP~0″ noparallel;
alter index  “VBRP~ZVG” rebuild online compress 2 parallel 4 pctfree 1;
alter index  “VBRP~ZVG” noparallel;
alter index  “VRPMA~0″ rebuild online compress 6 parallel 4 pctfree 1;
alter index  “VRPMA~0″ noparallel;
alter index  “EKET~0″ rebuild online compress 2 parallel 15 nologging  pctfree 1;
alter index  “EKET~0″ noparallel;
alter index  “EKET~E” rebuild online compress 3 parallel 15 nologging  pctfree 1;
alter index  “EKET~E” noparallel;
alter index  “EKBE~0″ rebuild online compress 4 parallel 15 nologging pctfree 1;
alter index  “EKBE~0″ noparallel;
alter index  “EKBE~A” rebuild online compress 3 parallel 15 nologging pctfree 1;
alter index  “EKBE~A” noparallel;
alter index  “EKBE~B” rebuild online compress 2 parallel 15 nologging pctfree 1;
alter index  “EKBE~B” noparallel;
alter index  “EKBE~ZA” rebuild online compress 3 parallel 15 nologging pctfree 1;
alter index  “EKBE~ZA” noparallel;
alter index  “EKBE~ZE” rebuild online compress 3 parallel 15 nologging pctfree 1;
alter index  “EKBE~ZE” noparallel;
alter index  “MARDH~0″ rebuild online compress 2 parallel 15 nologging pctfree 1;
alter index  “MARDH~0″ noparallel;
alter index  “MARCH~0″ rebuild online compress 2 parallel 15 nologging pctfree 1;
alter index  “MARCH~0″ noparallel;
alter index  “S120~0″ rebuild online compress 6 parallel 15 nologging  pctfree 1;
alter index  “S120~0″ noparallel;
alter index  “S120~VAB” rebuild online compress 2 parallel 15 nologging   pctfree 1;
alter index  “S120~VAB” noparallel;
alter index  “MARDH~0″ rebuild online compress 2 parallel 4 pctfree 1;
alter index  “MARDH~0″ noparallel;
alter index  “VBFA~0″ rebuild online compress 3 parallel 15 nologging pctfree 1;
alter index  “VBFA~0″ noparallel;
alter index  “VBFA~ZDL” rebuild online compress 1 parallel 15 nologging   pctfree 1;
alter index  “VBFA~ZDL” noparallel;
alter index  “MBEWH~0″ rebuild online compress 4 parallel 15 nologging  pctfree 1;
alter index  “MBEWH~0″ noparallel;

1 comment:

Shruti Ramalingam said...

Thanks for the great information ,was looking for this information from long.Great blog SAP Basis Training in Chennai