Re: Compression failing for partition object

Below blog shows a similar example(though its range-hash but not exactly like your list-range example) but it seems the subpartition level rebuild worked after setting/modifying the compression property at partition level. Have you tried that way?

https://raajeshwaran.blogspot.com/2021/02/?m=0

On Tue, Aug 3, 2021 at 9:24 PM Lok P <loknath.73_at_gmail.com> wrote:

> Hi All, We got one scenario from the Dev, in which the compression of a
> composite partitioned index is giving error while doing in the subpartition
> granularity. I see a few blogs stating it has to be defined at partition
> level first with modify keyword but even that is also not working. Is this
> compress operation for individual partitions/subpartitions not allowed? And
> the only choice is to drop the whole index and then create it as compress
> enabled at one shot which is going to be resource intensive? Below is the
> sample test case which I tried below on a 19C database, and it failed.
>
> CREATE TABLE USER1.compression_test1
> PARTITION BY LIST (OBJECT_TYPE)
> SUBPARTITION BY RANGE (DATA_OBJECT_ID)
> ( PARTITION P_INDX_SUBPART VALUES ('INDEX SUBPARTITION')
> ( SUBPARTITION p_nw_low VALUES LESS THAN (1000)
> , SUBPARTITION p_nw_extraordinary VALUES LESS THAN (MAXVALUE)
> )
> , PARTITION P_TABLE VALUES ('TABLE')
> ( SUBPARTITION p_sw_low VALUES LESS THAN (1000)
> , SUBPARTITION p_sw_extraordinary VALUES LESS THAN (MAXVALUE)
> )
> )
> as select * from dba_objects where object_type in ('INDEX
> SUBPARTITION','TABLE') ;
>
> Create index USER1.CMPRS_IDx1 on
> USER1.compression_test1(DECODE(object_id,NULL,1,NULL)) local parallel 4;
>
> alter index USER1.CMPRS_IDx1 rebuild subpartition p_nw_low compress;
>
> ERROR at line 1:
> ORA-14189: this physical attribute may not be specified for an index
> subpartition
>
> alter index USER1.CMPRS_IDx1 modify subpartition p_nw_low compress;
>
> ERROR at line 1:
> ORA-14193: invalid ALTER INDEX MODIFY SUBPARTITION option
>
> alter index USER1.CMPRS_IDx1 modify partition P_TABLE compress;
>
> ERROR at line 1:
> ORA-28659: COMPRESS must be specified at object level first
>
> alter index USER1.CMPRS_IDx1 modify compress;
>
> *
> ERROR at line 1:
> ORA-02243: invalid ALTER INDEX or ALTER MATERIALIZED VIEW option
>

--
http://www.freelists.org/webpage/oracle-l