Thank you Jonathan and Pap.
I think I am stuck. As Pap mentioned, it seems we won't be able to restrict
the compression at subpartition to make it restrict to one day range
subpartition only. So no meaning in checking the feasibility of directly
achieving compression at partition/subpartition level. Which means the only
option left would be to drop and create the index as local compressed. And
we may have to validate what level of degradation we are going to see in
the dml performance on the live partition in presence of a compressed index
and if that is acceptable or not.
But yes at least , it's learning and by using this "Advanced low"
compression while creating the compressed index we can go partition by
partition which would be in a controlled fashion rather than in one shot
drop and create. But again the real index we have is a function based
index. This advanced low option does not work for function based indexes
too :), i tested and below doc states that too. Also as I read through the
advanced low option , it looks like there is not much difference between
basic compression vs advanced low except few restrictions on advanced low
and also this 'advanced low' is better wrt compression ratio as compared to
basic compression.
https://www.oracle.com/a/tech/docs/advanced-compression-poc-insights.pdf
So basically in this case, we don't have much of an option but to drop and
create the local function based indexes with basic compression in one
shot(no partition by partition/subpartition possible). And have to live
with whatever DML performance degradation it will have on the live
subpartition. Please correct me if I'm interpreting anything wrong here.
On Thu, Aug 5, 2021 at 4:25 AM Jonathan Lewis <jlewisoracle@xxxxxxxxx>
wrote:
Here's the corresponding page on the 12.1 manual - it says exactly theActual requirement was on a 12.1 version database
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.
