Re: Compression failing for partition object
Date: Thu, 5 Aug 2021 01:23:31 +0530
Message-ID: <CAKna9VYr2Qg-_MUWEW6wsxOBVJWUMvfxFMnB8B-MEb880mAWEw_at_mail.gmail.com>
Thank You Jonathan. Actual requirement was on a 12.1 version database. And the table was a list - range partition table with size ~2TB with the local index which is planned for compression is of size ~200GB and its a local composite index on 4 columns. Now we don't want to keep the compression enabled on the live partition which is exposed to DML but want to compress historical ones which are less exposed to DML and mostly exposed to read queries.
When you said "*the index should have been defined globally with the appropriate type of compression.*'' Does it mean that we have to drop the existing index fully and create it as local compressed from scratch? Actually as the index is already exists as nocompress, so we were thinking why to drop and create as compress , if we can directly compress the subpartition/partitions as per our wish keeping the live partitions untouched/uncompressed and when new range sub-partition i.e. live sub-partition will be added it will be created as uncompressed only but later on will be compressed.
But yes,I think the option of globally defining the compression property i.e. drop and create the index as local compressed from scratch , will by default make all the newly created partitions+subpartitions in future as compressed only. And we have to make it alter nocompress and again do it compress after it moved out from active DML.
However, coming to the exact error which I was getting with the basic compression syntax while modifying the partition level compression property without defining at global level(i.e. drop + create index a compressed) . It's working fine with the ADVANCED LOW option. Of Course it's not allowed to define any subset of columns as the compression prefix but as a full set it's working fine. See below. And I am seeing the compression ratio is similar to the basic compression one. So I am not much aware about this option , but if we opt for this ADVANCED LOW option, does this have any demerits over the basic compression?
drop table USER1.compression_test1;
CREATE TABLE USER1.compression_test1
PARTITION BY LIST (OBJECT_TYPE) SUBPARTITION BY RANGE (DATA_OBJECT_ID) ( PARTITION P_INDX_SUBPART VALUES ('INDEX SUBPARTITION') TABLESPACE EIS_D01 ( SUBPARTITION p_nw_low VALUES LESS THAN (1000) TABLESPACE EIS_D01
, SUBPARTITION p_nw_extraordinary VALUES LESS THAN (MAXVALUE) TABLESPACE EIS_D01 )
, PARTITION P_TABLE VALUES ('TABLE') TABLESPACE EIS_D01 ( SUBPARTITION p_sw_low VALUES LESS THAN (1000) TABLESPACE EIS_D01
, SUBPARTITION p_sw_extraordinary VALUES LESS THAN (MAXVALUE) TABLESPACE EIS_D01 )
)
as select * from dba_objects where object_type in ('INDEX SUBPARTITION','TABLE') ; Create index USER1.CMPRS_IDx1 on USER1.compression_test1(object_type, object_id) local parallel 4;
SQL> alter index USER1.CMPRS_IDx1 modify partition P_TABLE compress;
alter index USER1.CMPRS_IDx1 modify partition P_TABLE compress
*
ERROR at line 1:
ORA-28659: COMPRESS must be specified at object level first
SQL> alter index USER1.CMPRS_IDx1 modify partition P_TABLE compress 2 advanced low;
Index altered.
SQL> alter index USER1.CMPRS_IDx1 rebuild subpartition p_sw_low;
Index altered.
SQL> alter index USER1.CMPRS_IDx1 rebuild subpartition p_sw_extraordinary;
Index altered.
alter index USER1.CMPRS_IDx1 modify partition P_INDX_SUBPART compress advanced low;
alter index USER1.CMPRS_IDx1 rebuild subpartition p_nw_low;
alter index USER1.CMPRS_IDx1 rebuild subpartition p_nw_extraordinary;
On Wed, Aug 4, 2021 at 2:29 PM Jonathan Lewis <jlewisoracle_at_gmail.com> wrote:
> When saying "I see a few blogs stating ..." please give the URL of at
> least one of those blogs.
> Here's a reference to the 19c SQL Language reference manual under ALTER
> INDEX ...
>
> https://mail.google.com/mail/u/0/?tab=wm#label/oracle-l/WhctKKWxbKDCdfzpWBzXvzfCvqzdxZwlJpRdFpNhkLpwMcSKQsXzXdTBLKmbLtZSkxcsPBV
>
> This states:
> Use this clause to change the compression attribute for the partition and
> every subpartition in that partition. Oracle Database marks each index
> subpartition in the partition UNUSABLE and you must then rebuild these
> subpartitions. Prefix compression must already have been specified for the
> index before you can specify the prefix_compression clause for a
> partition, or advanced index compression must have already been specified
> for the index before you can specify the advanced_index_compression
> clause for a partition. You can specify this clause only at the partition
> level. You cannot change the compression attribute for an individual
> subpartition.
>
> So what you're seeing is expected behaviour.
>
> You have to modify at the partition level but
> a) the index should have been defined globally with the appropriate type
> of compression.
> and
> b) when you modify the partition level all the subpartitions become
> unusable and have to be rebuilt (just "rebuild", no "compress xxx" - they
> will use the partition level compression).
> >
> What the manuals might not say is that for prefix compression the prefix
> length is recorded only at the global level, and the partition level says
> whether it is enabled or disabled, so you can't change the prefix length of
> an individual partiiton and its subpartitions.
> You may also find that when you change the compression level of advanced
> compression on a partition the COMPRESSION column in user_ind_partitions
> and user_ind_subpartition doesn't get modified until you rebuild the
> subpartitions.
>
> Regards
> Jonathan Lewis
> > > >
> On Tue, 3 Aug 2021 at 16:54, 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-lReceived on Wed Aug 04 2021 - 21:53:31 CEST