loss of space after reorg of compressed index

[Former Member]

Hi Oracle experts,

In 2008 we compressed indexes of ECC system as per sap note 1109743 and

obtained high compression ratio.

Now we have upgraded to oracle 11.2.0.2 and BRTOOLS 7.20 (10).

While using brtools to do table reorg/compression as per note 1431296

indexes loose compression factor.

before after compression

GLFUNCA PSAPGLFUNCA 41,574,400 11,468,800 enabled

GLFUNCA~0 PSAPSTABI 699,712 5,555,648 enabled

GLFUNCA~1 PSAPSTABI 263,872 2,112,384 enabled

GLFUNCA~2 PSAPSTABI 446,528 3,531,072 enabled

GLFUNCA~3 PSAPSTABI 1,051,904 5,047,872 enabled

I have tried uncompressing and compressing indexes again but cannot compress to

same factor, results are same using brtools or oracle command.

I have tested on coy of system where table GLFUNCA is uncompressed and indexes compressed.

reorg of indexes result in same bad compression factor.

key used for compression is same.

any advise, how to regain index compression factor or anyone having similar experirence.

[Former Member]

here is some more info for one index

before reorg

*Block levels.......... 3

*Leaf blocks........... 1,076,600

*Distinct keys......... 587

*Avg leaf blks per key. 4,871

*Avg data blks per key. 183,657

*Clustering factor..... 40,588,200

after reorg

*Block levels.......... 3

*Leaf blocks........... 264,046

*Distinct keys......... 515

*Avg leaf blks per key. 3,628

*Avg data blks per key. 297,675

*Clustering factor..... 20,837,281

[Former Member]

I have uncompressed and compressed again.indexes have same columns and compressed on same keys.

INDEX_NAME PREFIX_LENGTH

-

---

-

---

GLFUNCA~0 1

GLFUNCA~1 4

GLFUNCA~2 6

GLFUNCA~3 7

problem is only for this table , rest all are good.

[Former Member]

Did you pay attention if there were a different number of columns compressed? You can check the aktual compression with this statement:

SQL> select index_name, prefix_length from dba_indexes where table_name = 'GLPCA';

INDEX_NAME                     PREFIX_LENGTH
------------------------------ -------------
GLPCA~0                        <no of comp columns here>
GLPCA~1                        <no of comp columns here>
...

Cheers Michael

[jairo_pedroza]

Hello Daljit,

First time I heard about this kind of behavior. I already faced index with the same size after compress, but it happened because the number of colunm was wrong defined.

My suggestion:

1.Uncompress one index (rebuild without compress);

2.Identify number of column that should be compress;

3.Rebuild with compress nologging;

4.Active logging

Be aware all related below notes..

1289494 - FAQ Oracle compression

1109743 - Use of Index Key Compression for Oracle Databases

Regards,

Jairo Pedroza