RE: Compression failing for partition object

From: Dominic Brooks <dombrooks_at_hotmail.com>
Date: Wed, 4 Aug 2021 14:19:44 +0000
Message-ID: <DU2PR04MB8726170EBEE6BAA96B32AEFAA1F19_at_DU2PR04MB8726.eurprd04.prod.outlook.com>

There is a completely left-field exception (to the rule that index subpartition compression can only be inherited from partition) in that if you have a partition & subpartition with local compressed index which was created in 11.2.0.4 and then you upgrade to 19.6 and then you add another subartition to the pre-existing partition, then the index compression attribute for the new subpartition won’t match (it will be uncompressed).

Seems completely impossible to arrive at this situation manually/deliberately but about this time last year Oracle Support said this was not a bug 😊

Sent from Mail<https://go.microsoft.com/fwlink/?LinkId=550986> for Windows 10

From: Jonathan Lewis<mailto:jlewisoracle_at_gmail.com> Sent: 04 August 2021 09:59
To: Lok P<mailto:loknath.73_at_gmail.com> Cc: Oracle L<mailto:oracle-l_at_freelists.org> Subject: Re: Compression failing for partition object

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<https://emea01.safelinks.protection.outlook.com/?url=https%3A%2F%2Fmail.google.com%2Fmail%2Fu%2F0%2F%3Ftab%3Dwm%23label%2Foracle-l%2FWhctKKWxbKDCdfzpWBzXvzfCvqzdxZwlJpRdFpNhkLpwMcSKQsXzXdTBLKmbLtZSkxcsPBV&data=04%7C01%7C%7C0fc5aac0fc5d4e13fc3c08d957261e93%7C84df9e7fe9f640afb435aaaaaaaaaaaa%7C1%7C0%7C637636643560848188%7CUnknown%7CTWFpbGZsb3d8eyJWIjoiMC4wLjAwMDAiLCJQIjoiV2luMzIiLCJBTiI6Ik1haWwiLCJXVCI6Mn0%3D%7C1000&sdata=6%2BH0qlgKjNQCHCPFu6gmcXfKCu9tW2N83KnsEMp0VdI%3D&reserved=0>

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<mailto: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

Received on Wed Aug 04 2021 - 16:19:44 CEST