Re: Compression failing for partition object

 >> 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_at_gmail.com> wrote:

> Thank You Jonathan. Actual requirement was on a 12.1 version database.
>

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