Change partitioned index compression

From: gonzo <gbahut_at_gmail.com>
Date: Tue, 7 Aug 2012 07:49:56 -0700 (PDT)
Message-ID: <42a3dadf-448a-40df-9413-39400edef192_at_googlegroups.com>



Hi,
Working on Oracle 10gR2.

I have a list partitioned table and a local partitioned index: My_table(a , b, c, d, ....): partitioned by list on column "a". My_index(b,c,d): Locally partitioned on column "a", not unique, created with "COMPRESS 3".

According to the INDEX_STATS view the optimal compression for the index is 1, this is, only use column b to compress repeated values.

This is how the index looks like:

select PARTITION_NAME, STATUS, COMPRESSION from user_ind_partitions where index_name='MY_INDEX';

PARTITION_NAME                 STATUS   COMPRESS
------------------------------ -------- --------
P_1                            USABLE   ENABLED
P_2                            USABLE   ENABLED

Trying to rebuild any index partition returns the following error:

alter index MY_INDEX rebuild partition P_1 compress 1;

                                                    *    
ERROR at line 1:
ORA-14010: this physical attribute may not be specified for an index partition

However, the Oracle SQL reference manual shows that the above "alter index" expression should be valid.

Anyone have a clue of why this error is being raised? Any alternative other than dropping and recreating the index?

Thanks. Received on Tue Aug 07 2012 - 09:49:56 CDT

Original text of this message