Index subpartition

From: Dominic Brooks <dombrooks_at_hotmail.com>
Date: Thu, 6 Aug 2020 13:04:06 +0000
Message-ID: <DB7PR04MB44433507E684C996189FA0C3A14B0_at_DB7PR04MB4443.eurprd04.prod.outlook.com>

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

Received on Thu Aug 06 2020 - 15:04:06 CEST