Re: Compression failing for partition object

That make me wonder if you could create a simple partitioned table with a local index declared as compressed, with some partitions NOT compressed, then exchange the table, including indexes, a partition of a composite partitioned table (with a matching local compressed index) and end up with some subpartitions compressed and some not. (Or would you just get a "definition mismatch ?14098?) error message on the exchange.

Regards
Jonathan lewis

On Wed, 4 Aug 2021 at 15:19, Dominic Brooks <dombrooks_at_hotmail.com> wrote:

> 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 <jlewisoracle_at_gmail.com>
> *Sent: *04 August 2021 09:59
> *To: *Lok P <loknath.73_at_gmail.com>
> *Cc: *Oracle L <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> 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