Friday, January 20, 2012

ORA-14086: a partitioned index may not be rebuilt as a whole

SQL> ALTER INDEX REBUILD ONLINE NOLOGGING compress 2 storage (initial 8k next 8k);
            *
ERROR at line 1:
ORA-14086: a partitioned index may not be rebuilt as a whole

 
To compress (use key compression on) a partitioned index you first have to create the index with the compress keyword. Drop it and recreate!
 
If you try to alter the index:

SQL> ALTER INDEX  compress;

ORA-02243: ogiltigt argument för ALTER INDEX eller ALTER MATERIALIZED VIEW


After recreation (drop and create) you can compress each partition on the index if you like:


SQL> alter index rebuild
    partition compress 2;