Re: Compression failing for partition object

Yes, I think in your scenario the only option would be to test the degradation of DML performance on the live/current partition with the compressed index on a test environment and accordingly take a decision if that is okay compared to the gain you are getting for the read queries + space usage. And if it's okay then go for drop and create that index from scratch as fully local compressed index.

On Thu, Aug 5, 2021 at 2:01 PM Lok P <loknath.73_at_gmail.com> wrote:

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