Oracle Advance Compression

[former_member184628]

Hi All,

we have done compression on the Pre-production system and now starting PRD system this weekend.

I have gone through few posts like:

1436352 - Oracle Database 11g Advanced Compression for SAP Systems

1431296 - LOB conversion and table compression with BRSPACE 7.20

And others but still not very clear, now we have done compression in Pre-production and the DB size has been reduced to 60%. I can see that

SQL> SELECT count(*) FROM user_tables where compression='DISABLED';

  COUNT(*)

----------

       947

SQL> SELECT count(*) FROM user_tables where compression='ENABLED';

  COUNT(*)

----------

         0

How to enable Compression on user tables when we have already enabled Compression at Tablespace level(PSAPSR3Z and PSAPSR3700Z).

Another thing is " Is it required to enable compression for other Indexes also before reorg?" if yes then is there any way to do it in mass apart from doing it individually with "ALTER INDEX "<index_name>" REBUILD ONLINE COMPRESS 2;" for User_tables.

EX:

SQL> SELECT table_name, compression, compress_for FROM user_tables where compression='DISABLED';

TABLE_NAME                     COMPRESS COMPRESS_FOR                           

------------------------------ -------- ------------                           

LOB$                           DISABLED                                        

VIEWTRCOL$                     DISABLED                                        

ICOL$                          DISABLED                                        

REFCON$                        DISABLED                                        

ATTRCOL$                       DISABLED                                        

COLTYPE$                       DISABLED                                        

ICOLDEP$                       DISABLED                                        

LIBRARY$                       DISABLED                                        

IND$                           DISABLED                                        

TAB$                           DISABLED                                        

ASSEMBLY$                      DISABLED                                        

TABLE_NAME                     COMPRESS COMPRESS_FOR                           

------------------------------ -------- ------------                           

SUBCOLTYPE$                    DISABLED                                        

CLU$                           DISABLED                                        

OPQTYPE$                       DISABLED                                        

COL$                           DISABLED                                        

TYPE_MISC$                     DISABLED                                        

NTAB$                          DISABLED                                        

FET$                           DISABLED                                        

TS$                            DISABLED                                        

UET$                           DISABLED                                        

SEG$                           DISABLED                                        

USER$                          DISABLED        

++++++++++++++++++++++++++++++++++++++++++++++++++++++++

Is only a few example for your reference.

Thanks and Regards,

Sharib Tasneem

[former_member188883]

Hi Sharib,

Please check SAP Note 1436352 - Oracle Database 11g Advanced Compression for SAP Systems

Hope this helps.

Regards,

Deepak Kori

[former_member184628]

Hi Deepak,

I have already gone through the SAP note you have mentioned but I still have some doubt so I have raised this as a question in SCN.

Please refer to my post as I have already mentioned it there that I have referred to SAP note 1436352.

Can you provide me some more clarity on User_tables compression option.

Thanks,

Sharib

[former_member188883]

Hi Sharib,

My mistake to miss out the information shared in the beginning.

You specify table compression with the COMPRESS clause of the CREATE TABLE statement. You can enable compression for an existing table by using this clause in an ALTER TABLE statement. In this case, the only data that is compressed is the data inserted or updated after compression is enabled

Please also note the restrictions associated with table compression:

- Compressed tables can only have columns added or dropped if the COMPRESS FOR ALL OPERATIONS option was used.

- Compressed tables must not have more than 255 columns.

- Compression is not applied to lob segments.

- Table compression is only valid for heap organized tables, not index organized tables.

- The compression clause cannot be applied to hash or hash-list partitions. Instead, they must inherit their compression settings from the tablespace, table or partition settings.

- Table compression cannot be specified for external or clustered tables.

Hope this helps.

Regards,

Deepak Kori

[former_member184628]

Hi,

Thanks for your explanation.

This is a CRM Server and the biggest table is CRMD_ORDER_INDEX and and few other tables and  has over 15Million records. the Index is CRMD_ORDER_INDEX~0, can we comnpress only this index in DB using Alter Index command and as we have plan for reorg it will compress whole Index after that.

It is feasible? or we need to compress other indexes as our majority time is spend in searching on this table only.

Thanks,

Sharib