Re: Compression failing for partition object
- From: Jonathan Lewis <jlewisoracle@xxxxxxxxx>
- To: Oracle L <oracle-l@xxxxxxxxxxxxx>
- Date: Wed, 4 Aug 2021 23:54:11 +0100
Actual requirement was on a 12.1 version database
Here's the corresponding page on the 12.1 manual - it says exactly the same
thing as the 19c manual (I don't know what happened to the UTL I tried to
supply for the 19i reference - the thing that ended up in the email clearly
wasn't it)
https://docs.oracle.com/database/121/SQLRF/statements_1012.htm#BGBDJDEA
According to that paragraph from the manual you shouldn't have been able to
modify the partition to any type of compression because the index wasn't
declared as compressed. And the first working syntax you showed shouldn't
have worked at all with that mix of prefixed and andvanced compression:
SQL> alter index USER1.CMPRS_IDx1 modify partition P_TABLE compress 2
advanced low;
Index altered.
I have found, though, that on my 19c I can modify an index partition to
"compres advanced low" or "high" even when the index was not declared as
such - so maybe the manual (or this specific piece of this manual) is
wrong. The manuals do sometimes contradict each other.
So, contrary to the manuals, it looks as if you can create the index index
without compression, compress to "advanced low" all the existing partitions
and subpartiions - although this will presumably lock the table (or, at
least, the relevant [sub]partition) while the rebuilds are happening and
still have new [sub]partitions appearing without compression.
One word of warning - anything with the word "Advanced" in its name may be
subject to a separate licence, so check whether or not advanced indexed
compression is part of a separately licensed "advanced compression" option;
and if it is check that you have licensed it before you use it.
For more information on advanced index compression, Richard Foote's blog is
the best site to search - e.g google with
compress advanced low site:richardfoote.wordpress.com
Regards
Jonathan Lewis
On Wed, 4 Aug 2021 at 20:53, Lok P <loknath.73@xxxxxxxxx> wrote:
Thank You Jonathan. Actual requirement was on a 12.1 version database.
Other related posts:
