Re: Should one include partitioning key column as first column of non-unique local index

From: vsevolod afanassiev <vsevolod.afanassiev_at_gmail.com>
Date: Wed, 17 Aug 2011 00:23:50 -0700 (PDT)
Message-ID: <5c080457-d8eb-4510-81f1-a2365b98be97_at_t6g2000yqd.googlegroups.com>

Thanks, I didn't know that partitioned indexes can be compressed. As per manual (Oracle Database SQL Reference 10g Release 2 (10.2) B14200-02 December 2005, page 14-67):

"Oracle Database compresses only nonpartitioned indexes that are nonunique or unique indexes of at least two columns."

What would be syntax for compressing existing partitioned index?

I tried in 10.2.0.4:

SQL> alter index foo_ym_tx rebuild compress; alter index foo_ym_tx rebuild compress

            *
ERROR at line 1:
ORA-14086: a partitioned index may not be rebuilt as a whole

SQL> alter index foo_ym_tx rebuild partition p201101 compress; alter index foo_ym_tx rebuild partition p201101 compress *
ERROR at line 1:
ORA-28659: COMPRESS must be specified at object level first

SQL> alter index foo_ym_tx modify default attributes compress; alter index foo_ym_tx modify default attributes compress

                                                *

ERROR at line 1:
ORA-02243: invalid ALTER INDEX or ALTER MATERIALIZED VIEW option Received on Wed Aug 17 2011 - 02:23:50 CDT