Re: Compression failing for partition object

Only rebuilding individual partitions works fine like below. But the moment I add the compress keyword it fails.

Does it really work that way. I mean if Oracle allows the individual compression of index subpartition and say keeping few subpartitions uncompressed, it can lead to any unforeseen issue and thus, it's not allowed to compress subpartitions individually? Appreciate any comment on this.

alter index USER1.CMPRS_IDx1 rebuild subpartition p_nw_low;

On Wed, Aug 4, 2021 at 2:26 AM Lok P <loknath.73_at_gmail.com> wrote:

> Actually as i had already posted, i had tried that option but getting
> below error. Not sure why.
>
> alter index USER1.CMPRS_IDx1 modify partition P_TABLE compress;
>
> ERROR at line 1:
> ORA-28659: COMPRESS must be specified at object level first
>
>
>
>
>
> On Wed, 4 Aug 2021, 1:45 am Pap, <oracle.developer35_at_gmail.com> wrote:
>
>> 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