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@xxxxxxxxxxx>
Sent: 06 August 2020 20:36
To: ORACLE-L<mailto:oracle-l@xxxxxxxxxxxxx>
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@xxxxxxxxxxx>
Sent: 06 August 2020 14:04
To: ORACLE-L<mailto:oracle-l@xxxxxxxxxxxxx>
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
