RE: Index subpartition

Just to recap – partition and subpartition exists before upgrade, subpartition added after upgrade, local index has “wrong” compression attribute. New partitions are unaffected.

Finally got something back from Oracle Support – apparently this is expected behaviour related to parameter DB_INDEX_COMPRESSION_INHERITANCE which needs to be set for the upgrade (documentation gap).

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

From: Dominic Brooks<mailto:dombrooks_at_hotmail.com> Sent: 06 August 2020 20:36
To: ORACLE-L<mailto:oracle-l_at_freelists.org> Subject: RE: Index subpartition

I managed to test the theory I had at the end of previous email.

I took a pre-existing subpartitioned table (also list:list) with a local compressed index in an 11gR2 db and in the 19c database.

I added a new subpartition to an existing partition. In 11.2.0.4 the local index subpartition has COMPRESSION = ENABLED (like all the other subpartitions) In 19.6, the local index subpartition has COMPRESSION = DISABLED (unlike all the other subpartitions)

So probably grounds enough for further investigation and raising a ticket with Oracle Support.

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

From: Dominic Brooks<mailto:dombrooks_at_hotmail.com> Sent: 06 August 2020 14:04
To: ORACLE-L<mailto:oracle-l_at_freelists.org> Subject: Index subpartition

List List table partitioning on 19c.
Testing an upgrade. Mature code.
Subpartition exchange complaining about ORA-28665: table and partition must have same compression attribute. ALTER TABLE x EXCHANGE SUBPARTITION x_p1_sp1 WITH TABLE t_1 INCLUDING INDEXES WITHOUT VALIDATION

Investigation indicates that mismatch is at index level.

Both sides of the exchange have compressed index i1 For x, index i1 is local, compression = ENABLED, prefix_length = 1 At partition level it says, compression = ENABLED At subpartition level, within the same partition p1, some subpartitions say COMPRESSION = ENABLED, others say COMPRESSION=DISABLED (segment_created = NO)

My unreliable memory indicates that this shouldn’t be possible.

When I set up a noddy test case and try and reproduce on both 11gR2 and 19c, I get consistent COMPRESSION = ENABLED.

How might I have ended up with subpartitions of an index having different COMPRESSION settings?

I’m sure I’m missing some obvious command but I can’t think.

Just had a left field thought .... that partition and subpartitions with ENABLED were created BEFORE upgrade, the subpartitions with DISABLED were added to same partition AFTER upgrade. This isn’t a test I’ve attempted yet.... I should.

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

--
http://www.freelists.org/webpage/oracle-l