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@xxxxxxxxx> 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
